Repair orders query in SQL Server with subselects

Question

I was wondering if you guys know how to reduce the amount of subselects in this SQL Server Query? I think I'm using bad practices in this:

WITH TotalizedRepairOrders AS 
(SELECT SalesOrder,Items,DepartmentName, LastCompletedActivity,NextActivity,Plant,ParallelNextActivity FROM OrdersSynthesis)
SELECT Plant,
 DepartmentName,
 BlindsInQuestioning =( SELECT ISNULL(SUM(Items),0) FROM TotalizedRepairOrders WHERE NextActivity = 'NEW ORDER REVIEW' AND DepartmentName = RO.DepartmentName),
 BlindsReadyToCross =( SELECT ISNULL( SUM(Items),0) FROM TotalizedRepairOrders WHERE (LastCompletedActivity = 'LBM INVOICE' OR NextActivity = 'LBM INVOICE' OR NextActivity ='PLANT ARRIVAL') AND DepartmentName = RO.DepartmentName),
 BlindsPendingForShipmentCreation = ( SELECT ISNULL(SUM(Items),0) FROM TotalizedRepairOrders WHERE ( NextActivity = 'RETURN SHIPMENT CREATION') AND DepartmentName = RO.DepartmentName),
 PendingForInstructions = ( SELECT ISNULL(SUM(Items),0) FROM TotalizedRepairOrders WHERE ( ParallelNextActivity = 'INSTRUCTIONS' ) AND DepartmentName = RO.DepartmentName),
 PendingToBeRepaired = ( SELECT ISNULL( SUM(Items),0) FROM TotalizedRepairOrders WHERE ( NextActivity = 'PRODUCTION' ) AND DepartmentName = RO.DepartmentName),
 PendingForQualityControl = ( SELECT ISNULL(SUM(Items),0) FROM TotalizedRepairOrders WHERE ( NextActivity = 'INSPECTION') AND DepartmentName = RO.DepartmentName),
 PendingToBePacked = ( SELECT ISNULL(SUM(Items),0) FROM TotalizedRepairOrders WHERE ( NextActivity = 'PACKING LABEL') AND DepartmentName = RO.DepartmentName)
FROM TotalizedRepairOrders RO GROUP BY Plant ,DepartmentName 

• Tim LaVenice: Have you tried to pivot the subqueries? sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server

Answer 1

Here is a simpler method of your query, which doesn't use a CTE and a bunch of sub-selects.

SELECT
 Plant
 DepartmentName,
 SUM(CASE WHEN NextActivity = 'NEW ORDER REVIEW' THEN ISNULL(Items,0) ELSE 0 END) AS BLindsInQuestion,
 SUM(CASE WHEN LastCompletedActivity = 'LBM INVOICE' OR NextActivity = 'LBM INVOICE' OR NextActivity ='PLANT ARRIVAL' THEN ISNULL(Items,0) ELSE 0 END) as BlindsReadyToCross
 /*
 etc
 */
FROM 
 TotalizedRepairOrders
GROUP BY Plant ,DepartmentName 

• IVNSTN: Topsy-turvy a little. NextActivity is not in GROUP BY list and will cause compile error. SUM(CASE WHEN...)
• dnoeth: This is not going to return the expected result set, you need to do the CASE within SUM, not the SUM within CASE: SUM(CASE WHEN NextActivity = 'NEW ORDER REVIEW' THEN Items ELSE 0 END AS BlindsInQuestioning
• S3S: Yes, @IvanStarostin pointed that out I just haven't had time to fix it. This was migrated from StackOverFlow. I don't monitor CodeReview that often.