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

I need to run a report and show data for the last 2 year (24 months) per month. This means when I run the report at the end of July 2015, it should show data from August 2013 - July 2015. The sorting should look like this:
Aug 2013
Aug 2014
Sep 2013
Sep 2014
.....
Dec 2013
Dec 2014
Jan 2014
Jan 2015
.....
Jun 2014
Jun 2015
Jul 2014
Jul 2015

Any idea of how I can make this happen without having my report taking hours to run?
AletiaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
order by MONTH(date_column), YEAR(date_column)

I could give  more precise answer if there were more facts, such as your current query
PortletPaulEE Topic AdvisorCommented:
oh, but you also want to use a "financial year" it seems. Is August "month 1"?
Vikas GargAssociate Principal EngineerCommented:
Hello,

select * from table
where date_column >= >= DATEADD(D,1,DATEADD(M,-24,EOMONTH(GETDATE())))
order by MONTH(date_column), YEAR(date_column)
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

AletiaAuthor Commented:
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.
AletiaAuthor Commented:
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.
Vikas GargAssociate Principal EngineerCommented:
Try this

select * from table
where date_column >= DATEADD(D,1,DATEADD(M,-24,EOMONTH(GETDATE())))
order by year(date_column),month(date_column)
PortletPaulEE Topic AdvisorCommented:
>>"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
AletiaAuthor Commented:
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
PortletPaulEE Topic AdvisorCommented:
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

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.
AletiaAuthor Commented:
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.
AletiaAuthor Commented:
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.
AletiaAuthor Commented:
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?
PortletPaulEE Topic AdvisorCommented:
You should avoid using functions on data to assist in filtering. This destroys the ability to use indexes on those columns.

so instead of this
DECLARE @Extract_Date varchar(11) = '31 JUL 2015'

...
WHERE   DATEDIFF(M,Claim_Date,@Extract_Date) BETWEEN 0 AND 23
do this
DECLARE @Extract_Date datetime = '20150801'

....
WHERE ( Claim_Date >= dateadd(m,-24,@Extract_Date) AND Claim_Date < @Extract_Date )

see:
sargable: Search ARGument ABLE
http://en.wikipedia.org/wiki/Sargable

Rules of thumb
Avoid applying functions on data values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.

& "Beware of Between"

===========
So, I would propose this:
DECLARE @Extract_Date datetime = '20150801'

SELECT
       Display_Month, claim_month, claim_year
FROM tb_Claim_Detail
cross apply (select 
                 case when month(Claim_Date) < 8 
                      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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AletiaAuthor Commented:
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

Open in new window


Thanks so much for your comments!
PortletPaulEE Topic AdvisorCommented:
case when month(Claim_Date) < MONTH(@Extract_Date)
nice! well done.

Cheers, Paul

oh, don't forget to close off the question please
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.