sqlteradatateradata-sql-assistant

How to create a case statement which groups fields?


enter image description here

I am trying to understand how to group values together to add an indicator. I want to 'fix' the values and based on this, attribute an indicator.

The values I am trying to group are date, customer name and product type to create an indicator which captures what kind of order was placed (fruit only, fruit and vegetable, vegetable only). The goal is to calculate the total volume of each kind of order placed. The data is set out like this, and the column I am trying to create is the 'Order Type.

What I have done so far:

Case Statements

    CASE WHEN Product_Type = 'Fruit' THEN 1 ELSE 0 END AS Fruit_Indicator
, CASE WHEN Product_Type = 'Vegetable' THEN 1 ELSE 0 END AS Veg_Indicator

Case Statement with partition by and order by

, CASE WHEN ROW_NUMBER() OVER (PARTITION BY Order_Date, Customer  ORDER BY Order_Date ASC) = 1  AND Product_Type =  'Fruit' THEN 1 ELSE NULL END AS Fruit_Ind
, CASE WHEN ROW_NUMBER() OVER (PARTITION BY Order_Date, Customer  ORDER BY Order_Date ASC) = 1 AND Product_Type =  'Vegetable' THEN 1 ELSE NULL END AS Veg_Ind

I would appreciate any guidance on the right direction.

Thanks!


Solution

  • It APPEARS you are trying to get data grouped by date such as Mar 21, Mar 22, etc... So, you may want to have a secondary query to join the primary data from. The second query will be an aggregate by customer and date. If the date field is date/time oriented, you will have to adjust the group by to get proper formatted context such as date-format using month/day/year and ignoring any time component. This might also be handled by a function to just get the date-part and ignoring the time. Then, your original data to the aggregate should get you what you need. Maybe something like.

    select
          yt.date,
          yt.customer,
          yt.product,
          yt.productType,
          case when PreQuery.IsFruit > 0 and PreQuery.IsVegetable > 0
               then 'Fruit & Vegetable'
               when PreQuery.IsFruit > 0 and PreQuery.IsVegetable = 0
               then 'Fruit Only'
               when PreQuery.IsFruit = 0 and PreQuery.IsVegetable > 0
               then 'Vegetable Only' end OrderType
       from
          YourTable yt
             JOIN
             ( select
                     yt2.customer,
                     yt2.date,
                     max( case when yt2.ProductType = 'Fruit'
                               then 1 else 0 end ) IsFruit,
                     max( case when yt2.ProductType = 'Vegetable'
                               then 1 else 0 end ) IsVegetable
                  from
                     YourTable yt2
                  -- if you want to restrict time period, add a where
                  -- clause here on the date range as to not query entire table
                  group by
                     yt2.customer,
                     yt2.date ) PreQuery
                ON yt.customer = PreQuery.customer
               AND yt.date = PreQuery.date
       -- same here for your outer query to limit just date range in question.
       -- if you want to restrict time period, add a where
       -- clause here on the date range as to not query entire table
       order by
          yt.date,
          yt.customer,
          yt.product