Case and Group By 1

[LV]

I have this SQL statement:

SELECT    
	SUM([Time].HoursWorked) AS HoursWorked, 	
	CASE
		WHEN Role.Description='Billing Coordinator' OR Role.Description='Staff Accountant' OR Role.Description='Financial Analyst' THEN 'AT'
		WHEN Role.Description='Creative Director' OR Role.Description='Creative Designer' THEN 'CR'
		WHEN Role.Description='Technology Specialist' OR Role.Description='Sr. IT Developer' THEN 'IT'
		WHEN Role.Description='Account Manager' THEN 'AM'
		WHEN Role.Description='Lead Program Manager' OR Role.Description='Program Manager' THEN 'LPM'
		WHEN Role.Description='Program Assistant' THEN 'PA'
		ELSE 'Other'
	END 'RoleName'
FROM         
	[Time] INNER JOIN TimeBlock ON 
	[Time].TimeBlockId = TimeBlock.TimeBlockId INNER JOIN Project ON 
	TimeBlock.ProjectId = Project.ProjectId INNER JOIN Role ON 
	[Time].RoleId = Role.RoleId INNER JOIN Team ON 
	Role.TeamId = Team.TeamId
WHERE    Project.ProjectId = 88
GROUP BY RoleName

which gives me the "Invalid column name 'RoleName'" error. What I need to see is HoursWorked summed by 'RoleName':
Thanks in advance!

 

[lespaul]

I think you need 'AS' in front of 'Role Name':

END AS 'RoleName'

and I would change all the OR to IN statements (easier to read!):

CASE
WHEN Role.Description IN ('Billing Coordinator', 'Staff Accountant', 'Financial Analyst') THEN 'AT'
WHEN Role.Description IN ('Creative Director', 'Creative Designer') THEN 'CR'

etc...



Leslie

 

[LV]

Thanks lespaul, but it still gives me the same error. What helped is this:

SELECT    
	SUM([Time].HoursWorked) AS HoursWorked, 	
	CASE
		WHEN Role.Description='Billing Coordinator' OR Role.Description='Staff Accountant' OR Role.Description='Financial Analyst' THEN 'AT'
		WHEN Role.Description='Creative Director' OR Role.Description='Creative Designer' THEN 'CR'
		WHEN Role.Description='Technology Specialist' OR Role.Description='Sr. IT Developer' THEN 'IT'
		WHEN Role.Description='Account Manager' THEN 'AM'
		WHEN Role.Description='Lead Program Manager' OR Role.Description='Program Manager' THEN 'LPM'
		WHEN Role.Description='Program Assistant' THEN 'PA'
		ELSE 'Other'
	END 'RoleName'
FROM         
	[Time] INNER JOIN TimeBlock ON 
	[Time].TimeBlockId = TimeBlock.TimeBlockId INNER JOIN Project ON 
	TimeBlock.ProjectId = Project.ProjectId INNER JOIN Role ON 
	[Time].RoleId = Role.RoleId INNER JOIN Team ON 
	Role.TeamId = Team.TeamId
WHERE    Project.ProjectId = 88
GROUP BY
		(CASE
		WHEN Role.Description='Billing Coordinator' OR Role.Description='Staff Accountant' OR Role.Description='Financial Analyst' THEN 'AT'
		WHEN Role.Description='Creative Director' OR Role.Description='Creative Designer' THEN 'CR'
		WHEN Role.Description='Technology Specialist' OR Role.Description='Sr. IT Developer' THEN 'IT'
		WHEN Role.Description='Account Manager' THEN 'AM'
		WHEN Role.Description='Lead Program Manager' OR Role.Description='Program Manager' THEN 'LPM'
		WHEN Role.Description='Program Assistant' THEN 'PA'
		ELSE 'Other'
	END )
ORDER BY RoleName

I'm just wondering if there is a cleaner way of doing this.

 

[lespaul]

Didn't notice you using the alias in the GROUP BY!

You're right, you need the whole case statement in the GROUP BY. (Still might think about using the IN statement!)

leslie

 

[swampBoogie]

select Sum(HoursWorked), roleName from (
SELECT    
    [Time].HoursWorked,     
    CASE
        WHEN Role.Description='Billing Coordinator' OR Role.Description='Staff Accountant' OR Role.Description='Financial Analyst' THEN 'AT'
        WHEN Role.Description='Creative Director' OR Role.Description='Creative Designer' THEN 'CR'
        WHEN Role.Description='Technology Specialist' OR Role.Description='Sr. IT Developer' THEN 'IT'
        WHEN Role.Description='Account Manager' THEN 'AM'
        WHEN Role.Description='Lead Program Manager' OR Role.Description='Program Manager' THEN 'LPM'
        WHEN Role.Description='Program Assistant' THEN 'PA'
        ELSE 'Other'
    END RoleName
FROM         
    [Time] INNER JOIN TimeBlock ON
    [Time].TimeBlockId = TimeBlock.TimeBlockId INNER JOIN Project ON
    TimeBlock.ProjectId = Project.ProjectId INNER JOIN Role ON
    [Time].RoleId = Role.RoleId INNER JOIN Team ON
    Role.TeamId = Team.TeamId
WHERE    Project.ProjectId = 88) dt
GROUP BY RoleName

 

[LV]

swampBoogie: you got it, thanks a lot!