0
\$\begingroup\$

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 
\$\endgroup\$
1

1 Answer 1

1
\$\begingroup\$

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.