August 19, 2009 at 4:21 am
Hi I need help with this query please.
I need to workout the monthly averages for headcount, terminations and transferout, so I wrote this little query which gives me totals but I just can't bring my head around to writing the proper query for the averages. Please help.
Select Period,
Headcount = SUM(CASE WHEN Headcount ='Headcount' THEN 1 ELSE 0 END),
Terminations = SUM(CASE WHEN Headcount = 'Termination' THEN 1 ELSE 0 END),
TransferOut = SUM(CASE WHEN Headcount ='TransferCluster' AND Transfertype = 'TransferOut' THEN 1 ELSE 0 END)
FROM DTIHeadcount LEFT OUTER JOIN ReportingStructure
ON DTIHeadcount.CostCenter = ReportingStructure.CostCenter
Where Period Like '2009%'
and Staffno is not null
Group By Period
August 19, 2009 at 4:46 am
what does your underlying data look like?
Can you post some sample data - I guess the Period is a date?
August 19, 2009 at 4:58 am
Well....
Period is a numeric field and headcount is varchar.
Period Staffno Headcount
200901 123456 Headcount
200901 345612 Termination
200901 654321 TransferCluster
So when I run my query above I get :-
PeriodHeadcountTerminationsTransferOut
20090118315 424 18
20090218407 392 12
20090318390 408 12
20090418336 276 14
20090518246 291 19
20090618131 320 14
20090718054 331 14
From which point I would like to calculate monthly averages for the three columns. I hope this answers your question
August 19, 2009 at 5:04 am
OK.
From that data we can see that the Headcount for 200901 (which I guess means January 2009) is 18315 - what do you expect the 'monthly average' for that to be?
Your data doesn't have headcount per day, so you can't mean the average headcount for the month based on daily totals - I'm a bit confused!
August 19, 2009 at 7:20 am
Yep!
I'm also confused. Iam trying to rewrite a report that was written in excel and the formulas used are just not adding up. To calculate turnover percentage they have avgTerm + avgtransfer / avgHeadcount.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply