I have a dataset with Sale event history, and i need to create a column to show if the sale is still active. My dataset looks like this:
item | original_price | sale1 | sale2 | sale3 | current_price
-----|----------------|-------|-------|-------|--------------
1 | 3.00 | 2.75 | ? | ? | 2.75
2 | 4.00 | ? | 3.50 | ? | 4.00
3 | 10.00 | 7.50 | ? | 8.50 | 8.50
in the case of item 1, the new field should say active since the item is still at the sale1 price. Item 2 should say nonactive, because the price is no longer at a sale price.
Item 3 was on sale, then was not, and is now on sale again active.
So, the SQL needs to pull in all of these fields, and create a new column by comparing current_price to the most recent salex variable.
I need to implement this into a current SQL process, but do not know how to do this syntactically. thanks.
This seems to match your defintion:
case
when coalesce(sale3, sale2, sale1) = current_price then 'active'
else 'nonactive'
end