Calculating Average - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculating Average

Author

Topic

Trybbe
Starting Member

27 Posts

Posted - 2009-08-19 : 06:27:04

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-19 : 06:59:22

[code]
Select Period,
Headcount = SUM(CASE WHEN Headcount ='Headcount' THEN 1 ELSE 0 END) * 1.0 / COUNT(*),
Terminations = SUM(CASE WHEN Headcount = 'Termination' THEN 1 ELSE 0 END) * 1.0 / COUNT(*),
TransferOut = SUM(CASE WHEN Headcount ='TransferCluster' AND Transfertype = 'TransferOut' THEN 1 ELSE 0 END) * 1.0 / COUNT(*)
FROM DTIHeadcount
LEFT OUTER JOIN ReportingStructure
ON DTIHeadcount.CostCenter = ReportingStructure.CostCenter
Where Period Like '2009%'
and Staffno is not null
Group By Period[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources