1

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.

ɐlǝx
  • 1,384
  • 2
  • 17
  • 22
pyll
  • 1,688
  • 1
  • 26
  • 44

1 Answers1

3

This seems to match your defintion:

case
   when coalesce(sale3, sale2, sale1) = current_price then 'active'
   else 'nonactive'
end
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Thank you @dnoeth. This works perfectly. I had an issue implementing, but it was due to how i was coding the missing values ('' vs NULL). – pyll Aug 30 '16 at 17:39