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

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
Asked:
starkstaring
  • 4
  • 2
1 Solution
 
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
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
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now