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!
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