pyll pyll - 3 months ago 9
SQL Question

Search and Compare Columns to Create New Columns (Teradata SQL)

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.

Answer

This seems to match your defintion:

case
   when coalesce(sale3, sale2, sale1) = current_price then 'active'
   else 'nonactive'
end
Comments