David - 30 days ago 5

SQL Question

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

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)
```