9/18/2015

How to short Month name value in their natural order from Jan to Dec


In mostly cases, where we are having data month wise directly without proper date and we need to show Month value in their natural order , than how can we sort them so for this if we apply order by clause to sort in their natural order i.e. Jan, Feb, Mar..Dec then order by clause will order them in alphabetically order so for this I have one trick to solve this sorting issue as:
Suppose this is our sample data:
UserID
Activty Month
12
January
14
July
34
October
38
May
40
September
44
December
47
August
52
June
58
March
59
November
64
April
91
February

 And if we apply order by clause on ActivtyMonth column than we will we getting such result:
UserID
Activty Month
64
April
47
August
44
December
91
February
12
January
14
July
52
June
58
March
38
May
59
November
34
October
40
September

 You can see here, SQL engine shorted data into alphabetically order of "Activity Month" value so here is the trick to solve it:
SELECT UserID ,ActivityMonth

,Case When ActivityMonth='January' Then 1

When ActivityMonth='February' Then 2

When ActivityMonth='March' Then 3

When ActivityMonth='April' Then 4

When ActivityMonth='May' Then 5

When ActivityMonth='June' Then 6

When ActivityMonth='July' Then 7

When ActivityMonth='August' Then 8

When ActivityMonth='September' Then 9

When ActivityMonth='October' Then 10

When ActivityMonth='November' Then 11

When ActivityMonth='December' Then 12 END as SortOrder

From #MonthData

ORDER by

Case When ActivityMonth='January' Then 1

When ActivityMonth='February' Then 2

When ActivityMonth='March' Then 3

When ActivityMonth='April' Then 4

When ActivityMonth='May' Then 5

When ActivityMonth='June' Then 6

When ActivityMonth='July' Then 7

When ActivityMonth='August' Then 8

When ActivityMonth='September' Then 9

When ActivityMonth='October' Then 10

When ActivityMonth='November' Then 11

When ActivityMonth='December' Then 12 END

The result will be as:
UserID
Activity Month
SortOrder
12
January
1
91
February
2
58
March
3
64
April
4
38
May
5
52
June
6
14
July
7
47
August
8
40
September
9
34
October
10
59
November
11
44
December
12

 Here you can see I have used a case to provide number for each month value so based on this number I have sorted them in their natural order and same logic works for large amount of data.
In SSRS report we can use same logic to sorting month name field to implement same logic with Switch() function in the sorting option of the textbox properties of SSRS tablix as:


=SWITCH(Fields!ActivityMonth.Value = "January", 1

Fields!ActivityMonth.Value = " February ", 2,

Fields!ActivityMonth.Value = "March", 3,

Fields!ActivityMonth.Value = "April", 4,

Fields!ActivityMonth.Value = "May", 5,

Fields!ActivityMonth.Value = "June", 6,

Fields!ActivityMonth.Value = "July", 7,

Fields!ActivityMonth.Value = "August", 8,

Fields!ActivityMonth.Value = "September", 9,

Fields!ActivityMonth.Value = "October", 10,

Fields!ActivityMonth.Value = "November", 11,

Fields!ActivityMonth.Value = "December", 12)
Thanks!