Rolling window SQL for 365 days

Question

First question here. I am trying to calculate a rolling number in SQL for the past 365 days. I have the following formula:

    Select Count (*) ID
From HSE_Meldingen
Where ((Bedrijf IN ('BE Civiel','Be Mechanisch','BE Overig','BE Vijzel') And Datum_incident > DATEADD(DAY,-365,GETDATE()) and Soort_Melding = 'Letselongeval EHBO met doktersverzorging') 

Now I have to add something extra. I want this formula to look back 365 days for every day over the past 5 years. So calculate a rolling window over a 365 day period with a look back off 5 years.

I hope i have explained this well enough.

Answer 1

I would create a calendar table (you can find plenty of examples how to create one), and then JOIN this table to your HSE_meldingen table.

e.g.

Select d.theDate, Count (*) ID
From AllDates d
LEFT JOIN HSE_Meldingen m
    ON m.HSE_Meldingen BETWEEN DATEADD(DAY,-365,d.TheDate) AND d.theDate 
    AND m.Bedrijf IN ('BE Civiel','Be Mechanisch','BE Overig','BE Vijzel') 
    and m.Soort_Melding = 'Letselongeval EHBO met doktersverzorging'
Where d.theDate > DATEADD(DAY,-365,GETDATE())
GROUP BY d.theDate