Incorrect Use Of CASE? - 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

General SQL Server Forums

New to SQL Server Programming

Incorrect Use Of CASE?

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2007-08-01 : 05:50:53

Hi
I am trying to calculate staff hours by term but my SQL is flawed. Take this bit as a starter.

SUM(CASE WHEN Term = '1' THEN 1 ELSE 0 END),

When the Term is 1 this tells it to take that as 1 but this is wrong because it then just takes that line as 1. What I need it to do is when the Term is 1 to take that line as whatever the duration is i.e 300, another line maybe 200 so its keeps summing, so for that staff member Term 1 = 500. Likewise for Term 2 and Term 3

Why I've done that I dont know but I'm struggling to think of how to do it now.

SELECT     
StaffRefNo, FirstName, Surname, Code, 
CAST(ContractHours AS DECIMAL(19, 0)) AS Contract, 
CAST(ContractHours * 0.44 AS DECIMAL(19, 0)) AS [Term 1 Target], 
[Term 1] AS [T1 Actual], 
CAST(ContractHours * 0.27 AS DECIMAL(19, 0)) AS [Term 2 Target], 
[Term 2] AS [T2 Actual], 
CAST(ContractHours * 0.29 AS DECIMAL(19, 0)) AS [Term 3 Target], 
[Term 3] AS [T3 Actual]

FROM         (SELECT     TOP 100 PERCENT StaffRefNo, FirstName, Surname, ContractHours, Code, 

[Term 1] = SUM(CASE WHEN Term = '1' THEN 1 ELSE 0 END), 
[Term 2] = SUM(CASE WHEN Term = '2' THEN 1 ELSE 0 END), 
[Term 3] = SUM(CASE WHEN Term = '3' THEN 1 ELSE 0 END)
                       
FROM          dbo.DBActualStaffhours WITH (NOLOCK)

GROUP BY dbo.DBActualStaffhours.StaffRefNo, dbo.DBActualStaffhours.FirstName, dbo.DBActualStaffhours.Surname,dbo.DBActualStaffhours.ContractHours, dbo.DBactualstaffhours.code

ORDER BY dbo.DBActualStaffhours.StaffREfNo) AS Term

Can anyone give me some guidance please on the best way to go?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-08-01 : 06:33:25

Post some sample data and required output

--------------------------------------------------
S.Ahamed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 06:38:04

Don't group by contracthours! Sum it up.

SELECT		d.StaffRefNo,
		d.FirstName,
		d.Surname,
		d.Code, 
		CAST(d.ContractHours AS DECIMAL(19, 0)) AS Contract, 
		CAST(d.ContractHours * 0.44 AS DECIMAL(19, 0)) AS [Term 1 Target],
		d.[Term 1] AS [T1 Actual],
		CAST(d.ContractHours * 0.27 AS DECIMAL(19, 0)) AS [Term 2 Target],
		d.[Term 2] AS [T2 Actual], 
		CAST(d.ContractHours * 0.29 AS DECIMAL(19, 0)) AS [Term 3 Target],
		d.[Term 3] AS [T3 Actual]
FROM		(
			SELECT		StaffRefNo,
					FirstName,
					Surname,
					Code, 
					SUM(ContractHours) AS ContractHours,
					SUM(CASE WHEN Term = '1' THEN ContractHours ELSE 0 END) AS [Term 1], 
					SUM(CASE WHEN Term = '2' THEN ContractHours ELSE 0 END) AS [Term 2], 
					SUM(CASE WHEN Term = '3' THEN ContractHours ELSE 0 END) AS [Term 3]
			FROM		dbo.DbActualStaffHours WITH (NOLOCK)
			GROUP BY	StaffRefNo,
					FirstName,
					Surname,
					Code
		) AS d
ORDER BY	d.StaffRefNo

E 12°55'05.25"
N 56°04'39.16"

chipembele
Posting Yak Master

106 Posts

Posted - 2007-08-01 : 10:32:41

quote:

Originally posted by Peso

Don't group by contracthours! Sum it up.

SELECT		d.StaffRefNo,
		d.FirstName,
		d.Surname,
		d.Code, 
		CAST(d.ContractHours AS DECIMAL(19, 0)) AS Contract, 
		CAST(d.ContractHours * 0.44 AS DECIMAL(19, 0)) AS [Term 1 Target],
		d.[Term 1] AS [T1 Actual],
		CAST(d.ContractHours * 0.27 AS DECIMAL(19, 0)) AS [Term 2 Target],
		d.[Term 2] AS [T2 Actual], 
		CAST(d.ContractHours * 0.29 AS DECIMAL(19, 0)) AS [Term 3 Target],
		d.[Term 3] AS [T3 Actual]
FROM		(
			SELECT		StaffRefNo,
					FirstName,
					Surname,
					Code, 
					SUM(ContractHours) AS ContractHours,
					SUM(CASE WHEN Term = '1' THEN ContractHours ELSE 0 END) AS [Term 1], 
					SUM(CASE WHEN Term = '2' THEN ContractHours ELSE 0 END) AS [Term 2], 
					SUM(CASE WHEN Term = '3' THEN ContractHours ELSE 0 END) AS [Term 3]
			FROM		dbo.DbActualStaffHours WITH (NOLOCK)
			GROUP BY	StaffRefNo,
					FirstName,
					Surname,
					Code
		) AS d
ORDER BY	d.StaffRefNo

E 12°55'05.25"
N 56°04'39.16"

Thanks

I wasnt entirely clear. The contract hours is a static figure. The CAST(d.ContractHours * 0.27 AS DECIMAL(19, 0)) AS [Term 1 Target] is used to calculate target hours for that specific term. Its the duration we sum on (which is under the crosstabbed Term 1, 2, 3 headingsbelow) so I am looking for the following

Ref Name Contract Term 1 Term 2 Term 3
1 Joe Bloggs 800 320 300 165

I dont have access to my SQL Server now so cant check why the duration wasnt in my original query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources