How to create a case statement which groups fields?

Question

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:

• I originally completed this analysis in Tableau ]where I was able to use the 'Fixed' function and sum the value of indicators (for fruit or veggie) to determine each order type individually.
• I have written case statements to identify the product type, with the idea that I could sum this to determine order type (code below) however this did not work as I only need one instance of the indicator for each order. To solve this, I have written a case statement which partitions the fields and orders by date to get one instance of an indicator for each order.

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!

Answer 1

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