Satwik Nadkarny Satwik Nadkarny - 14 days ago 6
SQL Question

Custom sorting by month name in SQL Server

I have a table where for some dates a certain number of entries are placed. Here is the table structure :

ID EntryName Entries DateOfEntry
1 A 20 2016-01-17
2 B 22 2016-01-29
3 C 23 2016-02-17
4 D 19 2016-02-17
5 E 29 2016-03-17
6 F 30 2016-03-17
7 G 43 2016-04-17
8 H 10 2016-04-17
9 I 5 2016-05-17
10 J 120 2016-05-17
11 K 220 2016-06-17
12 L 210 2016-06-17
13 M 10 2016-07-17
14 N 20 2016-07-17
15 O 15 2016-08-17
16 P 17 2016-08-17
17 Q 19 2016-09-17
18 R 23 2016-09-17
19 S 43 2016-10-17
20 T 56 2016-10-17
21 U 65 2016-11-17
22 V 78 2016-11-17
23 W 12 2016-12-17
24 X 23 2016-12-17
25 Y 43 2016-02-17
26 Z 67 2016-03-17
27 AA 35 2015-01-17
28 AB 23 2015-01-29
29 AC 43 2015-02-17
30 AD 35 2015-02-17
31 AE 45 2015-03-17
32 AF 23 2015-03-17
33 AG 43 2015-04-17
34 AH 19 2015-04-17
35 AI 21 2015-05-17
36 AJ 13 2015-05-17
37 AK 22 2015-06-17
38 AL 45 2015-06-17
39 AM 66 2015-07-17
40 AN 77 2015-07-17
41 AO 89 2015-08-17
42 AP 127 2015-08-17
43 AQ 19 2015-09-17
44 AR 223 2015-09-17
45 AS 143 2015-10-17
46 AT 36 2015-10-17
47 AU 45 2015-11-17
48 AV 28 2015-11-17
49 AW 72 2015-12-17
50 AX 24 2015-12-17
51 AY 46 2015-02-17
52 AZ 62 2015-03-17


The column
EntryName
is the entry identifier, the column
Entries
has the total number of entries for the date specified in the column
DateOfEntry
.

I am trying to formulate a query where the total number of entries are displayed on a month-wise basis. I currently have this query :

SELECT DateName(MONTH, e.DateOfEntry) AS MonthOfEntry,
MONTH(e.DateOfEntry) AS MonthNumber,
SUM(e.Entries) AS TotalEntries
FROM #entry e
GROUP BY MONTH(e.DateOfEntry), DateName(MONTH,e.DateOfEntry)
ORDER BY MONTH(e.DateOfEntry) ASC


which works fine as far as displaying the results are concerned. However, my issue here is that I need to sort the results on a month-wise basis where the starting month would be dynamic i.e. arising from a parameter (supplied by the user).

This means that if the user selects May of 2015 the results should be sorted from May 2015 to April 2016. Similarly, if the user selects October 2015, the results would be displayed from October 2015 to September 2016.

How would I go about getting this condition within the
ORDER BY
clause ?

Answer

You can put an offset into the ORDER BY using modulo arithmetic. For April:

ORDER BY (MONTH(e.DateOfEntry) + 12 - 4) % 12
--------------------------------------^ month number to start with

(The + 12 is simply so I don't have to remember if % returns negative numbers with negative operands.)

If you want the results chronologically, you can instead do:

ORDER BY MIN(e.DateOfEntry)