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

Have you tried to pivot the subqueries? sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server — Tim LaVenice, Commented Jun 27, 2016 at 18:52

S3S · Accepted Answer · 2016-06-30 16:28:47Z

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

edited Jun 30, 2016 at 16:28

answered Jun 27, 2016 at 18:35

S3S

3361 silver badge9 bronze badges

1

\$\begingroup\$ Topsy-turvy a little. NextActivity is not in GROUP BY list and will cause compile error. SUM(CASE WHEN...) \$\endgroup\$
– IVNSTN
Commented Jun 27, 2016 at 18:54
\$\begingroup\$ 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 \$\endgroup\$
– dnoeth
Commented Jun 30, 2016 at 16:21
\$\begingroup\$ 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. \$\endgroup\$
– S3S
Commented Jun 30, 2016 at 16:33

Add a comment |

Stack Exchange Network

Repair orders query in SQL Server with subselects

1 Answer 1

Your Answer

Hot Network Questions

Repair orders query in SQL Server with subselects

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions