RustyHamster RustyHamster - 2 months ago 6
SQL Question

Month To date -1 year for comparing results

I would like to compare stats between this month and this month last year
I am using sql database Here is my code that gives me Month to date

COUNT(CASE WHEN (MONTH(pm.DateAppIssued) = MONTH(GETDATE()) AND YEAR(pm.DateAppIssued) = YEAR(GETDATE())) then 1 end) AS [Issued]


This will give me a number of Issued cases for
01/09/2016 - Getdate()

What i want is the number of Issued cases for last year
01/09/2015 - 30/09/2015

Thanks

Answer

Just remove 1 from YEAR(GETDATE())

COUNT(CASE WHEN (MONTH(pm.DateAppIssued) = MONTH(GETDATE()) AND
                  YEAR(pm.DateAppIssued) = YEAR(GETDATE())-1)  
      then 1 end) AS [Issued]