CODEWITHSUNDEEP
sqlsql-servercase
XCCH004
XCCH004

Reputation: 323

Alternative to using CASE in a GROUP BY

I am trying to find a more compact code form and faster way to execute a query that requires (or at least I think requires) grouping by TWO case statements

I've tried using OVER PARTITION but can't seem to get the desired result

SELECT 
    CASE
        WHEN AA.BUSINESS_SEGMENT LIKE 'YEPPERS%'
        THEN 'YES'
        ELSE 'NO'
    END 'YZY',
    CASE
        WHEN MONTH(POI.EX_FACTORY_DATE) >= 1 AND MONTH(POI.EX_FACTORY_DATE) <=3
        THEN
            'Q1'
        WHEN MONTH(POI.EX_FACTORY_DATE) >= 4 AND MONTH(POI.EX_FACTORY_DATE) <=6
        THEN
            'Q2'
        WHEN MONTH(POI.EX_FACTORY_DATE) >= 7 AND MONTH(POI.EX_FACTORY_DATE) <=9
        THEN
            'Q3'
        WHEN MONTH(POI.EX_FACTORY_DATE) >= 10 AND MONTH(POI.EX_FACTORY_DATE) <=12
        THEN
            'Q4'
    END 'QTR',
    SUM(POI.PO_ORDERED_QUANTITY) PO_QTY
FROM VW_PO_ITEM POI
LEFT JOIN VW_ARTICLE_ATTRIBUTES AA ON AA.ARTICLE = POI.MATERIAL 
WHERE POI.DEL_INDICATOR <> 'L' AND POI.EX_FACTORY_DATE BETWEEN '7/1/2019' AND '12/31/2019' AND ((POI.SHIPPING_INSTRUCT='A2' AND AA.BUSINESS_SEGMENT NOT LIKE 'YEPPERS%') OR AA.BUSINESS_SEGMENT LIKE 'YEPPERS%')
GROUP BY
    CASE
        WHEN AA.BUSINESS_SEGMENT LIKE 'YEPPERS%'
        THEN 'YES'
        ELSE 'NO'
    END, 
    CASE
        WHEN MONTH(POI.EX_FACTORY_DATE) >= 1 AND MONTH(POI.EX_FACTORY_DATE) <=3
        THEN
            'Q1'
        WHEN MONTH(POI.EX_FACTORY_DATE) >= 4 AND MONTH(POI.EX_FACTORY_DATE) <=6
        THEN
            'Q2'
        WHEN MONTH(POI.EX_FACTORY_DATE) >= 7 AND MONTH(POI.EX_FACTORY_DATE) <=9
        THEN
            'Q3'
        WHEN MONTH(POI.EX_FACTORY_DATE) >= 10 AND MONTH(POI.EX_FACTORY_DATE) <=12
        THEN
            'Q4'
    END 

--desired result (the query above does produce this) 

YZY      QTR  PO_QTY
-------- ---- ---------------------------------------
NO       Q4   389309
YES      Q4   649842
YES      Q3   843438
NO       Q3   20146

Upvotes: 0

Views: 162

Answers (2)

Parfait
Parfait

Reputation: 107577

Consider a CTE to handle unit level calculation before final aggregation, avoiding repetition. Also, look into DatePart to extract date parts from datetime fields:

WITH cte AS (
   SELECT CASE 
               WHEN AA.BUSINESS_SEGMENT LIKE 'YEPPERS%' 
               THEN 'YES' ELSE 'NO' 
          END [YZY], 
          DATEPART(qq, POI.EX_FACTORY_DATE) AS [QTR], 
          POI.PO_ORDERED_QUANTITY
   FROM VW_PO_ITEM POI 
   LEFT JOIN VW_ARTICLE_ATTRIBUTES AA 
       ON AA.ARTICLE = POI.MATERIAL 
   WHERE POI.DEL_INDICATOR <> 'L' 
     AND POI.EX_FACTORY_DATE BETWEEN '2019-07-01' AND '2019-12-31' 
     AND (
          (POI.SHIPPING_INSTRUCT='A2' 
           AND AA.BUSINESS_SEGMENT NOT LIKE 'YEPPERS%') 
          OR 
           AA.BUSINESS_SEGMENT LIKE 'YEPPERS%'
         ) 
  ) 

SELECT [YZY], [QTR],
       SUM(POI.PO_ORDERED_QUANTITY) AS PO_QTY 
FROM cte
GROUP BY [YZY], [QTR]

Upvotes: 1

GiovaniSalazar
GiovaniSalazar

Reputation: 2094

I think better is use DATEPART(quarter

FROM MyTable
GROUP BY DATEPART(quarter, columndate)

Upvotes: 4

Related Questions