Need Help with CASE/SUM in SQL

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

SQL Server Development (2000)

Need Help with CASE/SUM in SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-18 : 09:57:16

Avi writes "Thanks In Advance

I have the following SQL statement that returns SUM(Hours) per given week and workorder. i also want to get SUM(Hrs) and AVG(Hrs) for all eight weeks per workorder. is there a way to do all this in a SQL Statement.

additional fields that i need are
1. SUM(Wk1,Wk2,Wk3,Wk4,Wk5,Wk6,Wk7,Wk8) per workorder
2. AVG(Wk1,Wk2,Wk3,Wk4,Wk5,Wk6,Wk7,Wk8) per workorder

here is what i have right now

SELECT WO.Work_Order WO,
SUM(CASE WPH.WeekOf WHEN '3/11/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk1,
SUM(CASE WPH.WeekOf WHEN '3/18/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk2,
SUM(CASE WPH.WeekOf WHEN '3/25/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk3,
SUM(CASE WPH.WeekOf WHEN '4/1/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk4,
SUM(CASE WPH.WeekOf WHEN '4/8/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk5,
SUM(CASE WPH.WeekOf WHEN '4/15/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk6,
SUM(CASE WPH.WeekOf WHEN '4/22/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk7,
SUM(CASE WPH.WeekOf WHEN '4/29/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk8
FROM Workorder WO,WorkorderProjectionHours WPH,TeamMember TM,MemberDiscipline MD,Discipline DI, Assignments AI
WHERE WPH.Assignment_ID = AI.Assignment_ID AND AI.MemberDiscipline_ID = MD.MemberDiscipline_ID AND
MD.Member_ID = TM.Member_ID AND MD.Member_ID = 156 AND MD.Discipline_ID = DI.Discipline_ID AND WO.Work_Order = AI.Work_Order
GROUP BY WO.Work_Order"

Jay99

468 Posts

Posted - 2002-03-18 : 10:54:03

You may find that you get better performance from other methods . . .


SELECT WO.Work_Order WO,
SUM(CASE WPH.WeekOf WHEN '3/11/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk1, 
SUM(CASE WPH.WeekOf WHEN '3/18/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk2, 
SUM(CASE WPH.WeekOf WHEN '3/25/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk3, 
SUM(CASE WPH.WeekOf WHEN '4/1/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk4, 
SUM(CASE WPH.WeekOf WHEN '4/8/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk5, 
SUM(CASE WPH.WeekOf WHEN '4/15/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk6, 
SUM(CASE WPH.WeekOf WHEN '4/22/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk7, 
SUM(CASE WPH.WeekOf WHEN '4/29/2002' THEN WPH.Projected_Hours ELSE 0.00 END) AS Wk8,
(
CASE WPH.WeekOf WHEN '3/11/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '3/18/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '3/25/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '4/1/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '4/8/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '4/15/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '4/22/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '4/29/2002' THEN WPH.Projected_Hours ELSE 0.00 END
) as theSum,
((
CASE WPH.WeekOf WHEN '3/11/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '3/18/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '3/25/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '4/1/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '4/8/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '4/15/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '4/22/2002' THEN WPH.Projected_Hours ELSE 0.00 END +
CASE WPH.WeekOf WHEN '4/29/2002' THEN WPH.Projected_Hours ELSE 0.00 END) / 8
) as theAvg
FROM Workorder WO,WorkorderProjectionHours WPH,TeamMember TM,MemberDiscipline MD,Discipline DI, Assignments AI 
WHERE WPH.Assignment_ID = AI.Assignment_ID AND AI.MemberDiscipline_ID = MD.MemberDiscipline_ID AND 
MD.Member_ID = TM.Member_ID AND MD.Member_ID = 156 AND MD.Discipline_ID = DI.Discipline_ID AND WO.Work_Order = AI.Work_Order 
GROUP BY WO.Work_Order

Jay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources