Case Statement help - 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 2000 Forums

Transact-SQL (2000)

Case Statement help

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-05-06 : 12:55:59

I was trying to duplicate the results I get from an Excel Pivot table in SQL but came up with a small problem. Here is my Query:

SELECT losttime.WC_Injuries_2004.emp_no,
COUNT (losttime.WC_Injuries_2004.emp_LName)as 'Reported Claims',
CASE WHEN (losttime.WC_Injuries_2004.osha_recordable = 'Yes')THEN 1 ELSE 0 END 'Yosha',
CASE WHEN (losttime.WC_Injuries_2004.osha_recordable = 'No')THEN 1 ELSE 0 END 'Nosha',
CASE WHEN (losttime.WC_Injuries_2004.osha_recordable = 'Removed')THEN 1 ELSE 0 END 'Rosha'
From losttime.WC_Injuries_2004
JOIN losttime.Profit_Centers_Table ON losttime.WC_Injuries_2004.Emp_Station_Code = losttime.Profit_Centers_Table.StationCode
and losttime.WC_Injuries_2004.Emp_Company_Code = losttime.Profit_Centers_Table.CompanyCode
Join losttime.Branches_Table ON losttime.WC_Injuries_2004.Emp_Branch_Code = losttime.Branches_Table.BranchCode
Where losttime.WC_Injuries_2004.emp_station_code = '600'
GROUP BY losttime.WC_Injuries_2004.emp_station_code,
losttime.WC_Injuries_2004.emp_no,
losttime.WC_Injuries_2004.osha_recordable

I get the results I want with one exception. I have three employees who have filed two seperate claims each. This is correctly counted in the "Reported Claims" column, but the CASE count is off as it only gives a single count instead of showing the actual number.

ID Claims yes No Remove
1375 2 0 1 0
59802 2 1 0 0
130966 2 1 0 0

Any suggestions on how to come up with the correct total which is below:

ID Claims yes No Remove
1375 2 0 2 0
59802 2 2 0 0
130966 2 2 0 0

Thanks as always.

GC

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-06 : 13:30:39

sum(CASE WHEN (losttime.WC_Injuries_2004.osha_recordable = 'Yes')THEN 1 ELSE 0 END) [Yosha],

Notice also the [] as identifier delimitters. You don't need them as this is a valid identifier but is better than ''.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-05-06 : 18:28:45

NR,

Thanks for your assistance!

GC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources