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  
starkstaringAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ralmadaCommented:
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
starkstaringAuthor 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
ralmadaCommented:
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
						and (enddate >= dateadd(yy, year(getdate())-1900, dateadd(m, v.number, 0)) or 
						     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

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ralmadaCommented:
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
						and (enddate >= dateadd(yy, year(getdate())-1900, dateadd(m, v.number, 0)) or 
						     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

Open in new window

0
ralmadaCommented:
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
						and (enddate >= dateadd(yy, year(getdate())-1900, dateadd(ww, v.number, 0)) or 
						     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

Open in new window

0
starkstaringAuthor 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 
	(
		SELECT AVG(convert(numeric(8,2) , WeeklyHeadCount)) as AvgHeadCount, p.Period_UId
		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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.