Sorting dates to have the same months for different years next to each other

order by MONTH(date_column), YEAR(date_column)

I could give  more precise answer if there were more facts, such as your current query

oh, but you also want to use a "financial year" it seems. Is August "month 1"?

Hello,

select * from table
where date_column >= >= DATEADD(D,1,DATEADD(M,-24,EOMONTH(GETDATE())))
order by MONTH(date_column), YEAR(date_column)

My current query look something like this:
DECLARE      @Extract_Date VARCHAR(11)      = '31 JUL 2015'

SELECT             Diagnosis_Code AS Diagnosis_Type
                  ,LEFT(DATENAME(MONTH,Claim_Date),3) + ' ' + DATENAME(YEAR,Claim_Date) AS Display_Month
                  ,SUM(Claimed_Amount) AS Claimed
FROM             tb_Claim_Detail
WHERE             DATEDIFF(M,Claim_Date,@Extract_Date) BETWEEN 0 AND 24
GROUP BY       LEFT(DATENAME(MONTH,Claim_Date),3) + ' ' + DATENAME(YEAR,Claim_Date)

I tried sorting it in Data Tools (where I'm creating the SSRS report) by month and year, but it yields the following:

Jan 2014
Jan 2015
Feb 2014
Feb 2015
....
Aug 2013
Aug 2014
Sep 2013
Sep 2014
...
Dec 2013
Dec 2014

This is not the correct order. Given that my Extract Date is en 31 July 2015, my first date/month should be Aug 2013 and not Jan 2014.

Just a note: I don't care if I have to do the sorting in in my query or in Data Tools, but either way I couldn't find a way to get the order correct.

Try this

select * from table
where date_column >= DATEADD(D,1,DATEADD(M,-24,EOMONTH(GETDATE())))
order by year(date_column),month(date_column)

>>"Given that my Extract Date is en 31 July 2015, my first date/month should be Aug 2013 and not Jan 2014."
I think you have a problem,  with an extract date set as '31 JUL 2015'

DATEDIFF(M,Claim_Date,@Extract_Date) BETWEEN 0 AND 24

includes all of July 2013 and so the earliest month/year would be July 2013 (not August)

I repeat my earlier question. Is August "month 1"?
or, put another way, does the financial year start at 1st of August

Hi Vikas

I tired running my query the way you suggested:
DECLARE      @Extract_Date VARCHAR(11)      = '31 JUL 2015'

SELECT             YEAR(Claim_Date) AS Years
                  ,MONTH(Claim_Date) AS Months
                  ,LEFT(DATENAME(MONTH,Claim_Date),3) + ' ' + DATENAME(YEAR,Claim_Date) AS Display_Month
                  ,SUM(Claimed_Amount) AS Claimed
FROM             tb_Claim_Detail
WHERE             Claim_Date >= DATEADD(D,1,DATEADD(M,-24,EOMONTH(@Extract_Date)))
GROUP BY       YEAR(Claim_Date)
                  ,MONTH(Claim_Date)
                  ,LEFT(DATENAME(MONTH,Claim_Date),3) + ' ' + DATENAME(YEAR,Claim_Date)
ORDER BY       YEAR(Claim_Date)
                  ,MONTH(Claim_Date)


But it gives me the following results:
Years      Months      Display_Month      Claimed
2013      8            Aug 2013      454272.25
2013      9            Sep 2013      243344.78
2013      10          Oct 2013       294152.22
2013      11          Nov 2013      1161080.12
2013      12          Dec 2013      103732.46
2014      1            Jan 2014       282307.84
2014      2            Feb 2014      195081.99
2014      3            Mar 2014      214835.73
2014      4            Apr 2014      183398.84
2014      5            May 2014      282478.64
2014      6            Jun 2014       442385.27
2014      7            Jul 2014        237102.69
2014      8            Aug 2014      240253.61
2014      9            Sep 2014      535781.30
2014      10          Oct 2014       325542.15
2014      11          Nov 2014      244263.19
2014      12          Dec 2014      192735.78
2015      1            Jan 2015        792130.05
2015      2            Feb 2015      546516.50
2015      3            Mar 2015      440431.91
2015      4            Apr 2015      485177.19
2015      5            May 2015      354823.01
2015      6            Jun 2015        191574.30
2015      7            Jul 2015        304418.74
2015      8            Aug 2015      14996.47

Which is not correct for two reasons:
1. The second date/month should be Aug 2014 not Sep 2013
2. This includes Aug 2015 and it should stop at Jul 2015

I have not been able to test this, but please try this

DECLARE @Extract_Date varchar(11) = '31 JUL 2015'

SELECT
       Display_Month
     , claim_month
     , claim_year
     , SUM(Claimed_Amount) AS Claimed
FROM tb_Claim_Detail
cross apply (select 
                 case when month(Claim_Date) < 7 
                      then month(Claim_Date) + 12 
                      else month(Claim_Date) 
                 end as claim_month 
               , year(Claim_Date) as claim_year
               , LEFT(DATENAME(MONTH, Claim_Date), 3) 
                 + ' '
                 + DATENAME(YEAR, Claim_Date) AS Display_Month
            ) ca
WHERE DATEDIFF(M, Claim_Date, @Extract_Date) BETWEEN 0 AND 24
GROUP BY Display_Month, claim_month, claim_year
order by claim_month, claim_year

Select all Open in new window

I am not satisfied that the way you are selecting the date range is correct and I really don't like using a string 'dd mmm yyyy' as a means to declare a date.

In SQL Server the only truly safe date literal is in YYYYMMDD format

And, best practice for selecting date ranges is to AVOID using between, and to avoid using functions too.

Hi Paul

Sorry, yes, August is "month 1".
My query should be changed to
.....
WHERE   DATEDIFF(M,Claim_Date,@Extract_Date) BETWEEN 0 AND 23
......

This will give me data from 1 August 2013 to 31 July 2015 which is correct.

So, I just need to get the order right.

Paul, your query yields the following results:
Display_Month
Jul 2013
Jul 2014
Jul 2015
Aug 2013
Aug 2014
Sep 2013
Sep 2014
Oct 2013
Oct 2014
Nov 2013
Nov 2014
Dec 2013
Dec 2014
Jan 2014
Jan 2015
Feb 2014
Feb 2015
Mar 2014
Mar 2015
Apr 2014
Apr 2015
May 2014
May 2015
Jun 2014
Jun 2015

It looks like we're getting there - it's just that Jul 2013 is not supposed to be listed and
Jul 2014
Jul 2015
should be at the end. I'm going to play with your query to see if I can make it work.

Paul, this is not a query that I have written from scratch - I am just trying to use this query for my SSRS report. I would love to improve the query though - so, could you elaborate on your statement:
"In SQL Server the only truly safe date literal is in YYYYMMDD format

And, best practice for selecting date ranges is to AVOID using between, and to avoid using functions too."

Could you give me an example of how you would do it?

Paul this is perfect!! It is going to be so useful, thanks so much.

The only thing is that it needs to work for every month of the year since they will be running the report for the period Sep 2013 - Aug 2014 next month.
See the change I made to the case statement below. This will make it work for every month.

DECLARE @Extract_Date datetime = '20150801'

SELECT
       Display_Month, claim_month, claim_year
FROM tb_Claim_Detail
cross apply (select 
                 case when month(Claim_Date) < MONTH(@Extract_Date) 
                      then month(Claim_Date) + 12 
                      else month(Claim_Date) 
                 end as claim_month 
               , year(Claim_Date) as claim_year
               , LEFT(DATENAME(MONTH, Claim_Date), 3) + ' ' + DATENAME(YEAR, Claim_Date) AS Display_Month
            ) ca
WHERE ( Claim_Date >= dateadd(m,-24,@Extract_Date) AND Claim_Date < @Extract_Date )
GROUP BY Display_Month, claim_month, claim_year
order by claim_month, claim_year

Select all Open in new window

Thanks so much for your comments!

case when month(Claim_Date) < MONTH(@Extract_Date)
nice! well done.

Cheers, Paul

oh, don't forget to close off the question please