• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1420

# Moving Annual Total

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.

Regards
Kieron
0
starkstaring
• 4
• 2
1 Solution

Commented:
I'm sure that there's a better way of doing this. But can you please clarify what is that you're expecting the query to return.
Please post sample of both, the input and the output.
0

Author Commented:
People data - unique payroll number and start / end date (null if no end date)
Input
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

Output
Month     -     average headcount as at end of month
Month 1   -     5             (derived from 5+5+5+5 / 4)
Month 2   -     4.25        (derived from 5+4+4+4 / 4)
Month 3   -     3.75         (derived from 4+4+4+3 / 4)

The above headcount is at the end of each week, then averaged out across the month. From here, I can do a pivot to look at moving annual totals.
Month 1 2 3 etc.. are based on a financial calendar:
week_end_date       week_no      period_no
09/01/2010                49                   12
16/01/2010                50                   12
...

0

Commented:
So for the avg headcount as at end of month you could try like this:
``````select 	dateadd(yy, year(getdate())-1900, dateadd(m, v.number, 0)) as months,
(select count(Payroll) from yourtable where startdate <= dateadd(yy, year(getdate())-1900, dateadd(m, v.number+1, 0))-1
enddate is null)
) * 1.0 / month(getdate()) as avg headcount
from
master..spt_values v on v.type='P'
where v.number between 0 and month(getdate())-1
``````
0

Commented:
typo there
``````select 	dateadd(yy, year(getdate())-1900, dateadd(m, v.number, 0)) as months,
(select count(Payroll) from yourtable where startdate <= dateadd(yy, year(getdate())-1900, dateadd(m, v.number+1, 0))-1
enddate is null)
) * 1.0 / month(getdate()) as avg_headcount
from
master..spt_values v on v.type='P'
where v.number between 0 and month(getdate())-1
``````
0

Commented:
And if I understood you correctly, doing it per week could be something like this.

``````select 	dateadd(yy, year(getdate())-1900, dateadd(ww, v.number, 0)) as weeknumber,
(select count(Payroll) from yourtable where startdate <= dateadd(yy, year(getdate())-1900, dateadd(ww, v.number+1, 0))-1
enddate is null)
) * 1.0 / datepart(ww, getdate()) as avg headcount
from
master..spt_values v on v.type='P'
where v.number between 0 and datepart(ww, getdate())-1
``````
0

Author Commented:
finally found the answer, but thanks to those that helped.

``````SELECT convert(numeric(8,2), ISNULL(SUM(cntLeavers),0)), SUM(AvgHeadCount), a.Period_Uid
FROM
(
FROM
dim_FiscalCalendar_Period P
JOIN
(
SELECT count(distinct childpayrollno) WeeklyHeadCount, Week_Uid, p.period_uid
FROM #employees es
JOIN employee e ON e.Employee_Number = es.ChildPayrollNo
JOIN dim_FiscalCalendar_Week w on EndDate_Uid <= Day_Uid_BEGIN AND StartDate_Uid >= Day_Uid_BEGIN
JOIN dim_FiscalCalendar_period2 p on w.period_uid between p.startyearPeriod_Uid and p.period_uid
GROUP BY Week_Uid, p.Period_Uid
) weeklyHC ON p.Period_Uid = weeklyHC.Period_Uid
GROUP BY p.Period_UId
) a
LEFT OUTER JOIN
(
SELECT Count(distinct childpayrollno) as cntLeavers, Period_Uid
FROM employee e
JOIN #employees eh on e.employee_number = eh.childpayrollno
and e.end_date between CreatedDate and isnull(EndDate, end_date)
JOIN dim_FiscalCalendar_Period p on ISNULL(CONVERT(varchar(10), End_Date, 112), Day_Uid_END + 1) BETWEEN Day_Uid_BEGIN AND Day_Uid_END
WHERE end_date is not null
GROUP BY Period_uid
) y ON a.Period_Uid = y.Period_Uid
where a.period_uid = 201006
GROUP BY a.Period_Uid
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.