David David - 30 days ago 5
SQL Question

Recursive SQL Query for 6 individual months of following query

I'm wondering if there is a way to perform the following query recursively so it does so 6 times for the most recent 6 months.

SELECT DATENAME(MONTH,GETDATE()) AS 'Month',
SUM(CASE WHEN OverallRiskRating = 1 THEN 1 END) AS 'Low',
SUM(CASE WHEN OverallRiskRating = 2 THEN 1 END) AS 'Med',
SUM(CASE WHEN OverallRiskRating = 3 THEN 1 END) AS 'High'
FROM dbo.ChangeEvaluationForm
WHERE MONTH(DateSubmitted) = MONTH(GETDATE())


The results for this query are as follows

enter image description here

I'd like to return 5 more rows with data for each of the months prior to the current month. Is that something that's possible? I'd like to avoid performing 5 more individual queries if I can.

Thank you so much for your help in advance.

Answer Source

You can do this with a GROUP BY and using DATEADD() to move back 6 months from the current month:

Select   DateName(Month, DateSubmitted) As 'Month', 
         Sum(Case When OverallRiskRating = 1 Then 1 End) As 'Low',
         Sum(Case When OverallRiskRating = 2 Then 1 End) As 'Med',
         Sum(Case When OverallRiskRating = 3 Then 1 End) As 'High'
From     dbo.ChangeEvaluationForm
Where    DateSubmitted >= DateAdd(Month, DateDiff(Month, 0, DateAdd(Month, -6, GetDate())), 0)
Group By DateName(Month, DateSubmitted)