A bit new to SQL - the db is Snowflake, which I believe is ANSI
Main table shown below. Combinations of same Issue/UPC/Warehouse/Date can be possible, since a new record is added whenever a new issue is reported. Other columns exist, but should not affect this question
The exclude column is what I'm trying to figure out - it should be 'Y' if the desired combination of Issue/UPC/Warehouse and Date is in the Exclusion table, shown below.
The tricky part is the LEVEL column, defining if a UPC/Issue/Warehouse combination must match, or just UPC/Issue, or just UPC. Also, the records in the main table must fall within the Date range to be excluded.
Visually, expected result is this
This solution works for just one level (Issue/UPC/Warehouse), but I can't figure out how to do the other two without overlap and the possibility of excluding records on accident.
update t
set exclude = 'Y'
where exists (select 1
from exclusions e
where e.issue_code = t.issue_code and
e.upc = t.upc and
e.warehouse = t.warehouse and
t.date between e.date_from and e.date_to);
David's answer has covered the right approach to take, using the CASE
conditional expression, but ensure that your query also incorporates the level check into each condition explicitly. Here's a verbose example:
update t
set exclude = case
when exists(
select 1
from exclusions e
where
e.warehouse = t.warehouse
and e.upc = t.upc
and e.issue_code = t.issue_code
and t.date between e.date_from and e.date_to
and e.level = 'UPC/ISSUE/WAREHOUSE'
) then 'Y'
when exists(
select 1
from exclusions e
where
e.issue_code = t.issue_code
and e.upc = t.upc
and t.date between e.date_from and e.date_to
and e.level = 'UPC/ISSUE'
) then 'Y'
when exists(
select 1
from exclusions e
where
e.upc = t.upc
and t.date between e.date_from and e.date_to
and e.level = 'UPC'
) then 'Y'
else ''
end;