CODEWITHSUNDEEP
sqlsql-servert-sql
DooDooDoo2
DooDooDoo2

Reputation: 31

How to order by Month starting from a selected Month in SQL?

I have a table with a Code and Description column. The Description column contains the months of the year in consecutive order, and the code is the month number (e.g. Code = 1, Description = January).

I want to write a SQL query which takes the previous month (from the current month) and returns a table starting from that month and sorting months in ascending order (the previous month is August, so the table will start with August on row 1 with a code of 8 and next is September, 9 etc.)

My code so far:

DECLARE @PreviousMonth INT = MONTH(DATEADD(mm, -1, GETDATE()))
SELECT @PreviousMonth
SELECT
     MonthNumber AS Code,
     MonthName AS Description
FROM
    MonthName
ORDER BY MONTH(DATEADD(mm, -1, GETDATE()))

So if the current month is September, August will be the previous month.

Expected result (Code on the left, description on the right):

8 August
9 September 
10 October
11 November
12 December
1 January
2 February
3 March
4 April
5 May
6 June
7 July

Upvotes: 0

Views: 1070

Answers (2)

MatBailie
MatBailie

Reputation: 86706

ORDER BY
  MonthNumber + CASE WHEN MonthNumber < @PreviousMonth THEN 12 ELSE 0 END

Upvotes: 1

Stu
Stu

Reputation: 32579

If I understand correctly you need to be able select a range, which will "loop around" to the start.

One way is to union your table of months to produce a list from 1-23:

    declare @PreviousMonth int = Month(DateAdd(mm, -1, GetDate()));

    with m as (
        select code, description 
        from t 
        union all
        select code+12, description 
        from t
    )
    select Iif(m.code>12, m.code % 12,code) code, m.description
    from m
    where m.code between @PreviousMonth and @PreviousMonth+11
    order by m.code

Demo DB<>Fiddle

Upvotes: 1

Related Questions