How do you build a moving annual total query when you don't have the data at line level?
I am trying to create a SQL query that will give me a moving annual total figure for headcount across a rolling 12 month period.
The data I have is:
Payroll - Start Date - End Date
1234 - 23/10/2009 - 24/04/2010
2345 - 01/06/2009 - NULL
3456 - 02/03/2006 - 05/02/2010
4567 - 28/10/2005 - 01/04/2010
5678 - 14/10/2004 - 22/03/2010
The calculation is:
leavers / average headcount for period to date (12 months, by week)
I have to split the code into two parts. One is to create a temp table with the data arranged in a weekly format which I can then average out and use to divide number of leavers against.
the first part I'm thinking of something like: (w5 being current week)
payroll .....w48 - w49 - w50 - w51 - w52
1234 1 1 1 1 1
4567 1 1 0 0 0
withing having to repeat the following statement 52 times, I was wondering whether there's a better way of doing it?
--case when start_date <= @refdate and end_date >@refdate then 1 else 0 end as w52
@refdate being a weekending date in this example. The actual week ending dates I have in a separate financial calendar table.
The second part would be to sum the numbers up against each period to work out the averages for each month, and the 3rd (and easiest) would be to figure out the leavers for each month
I'm kind of struggling to know where to start, so any advice or guidance would be most appreciated.