a bit new to SQL and did some searching online but couldn't find an answer.
The SQL database is a Snowflake DB, which I believe is a ANSI db.
I have a fact table as outlined below. Combinations of same Issue/UPC/Warehouse/Date can be possible, since a new record is added whenever a new issue is reported.
The exclude column is what I'm trying to figure out - it should be 'Y' if the combo of Issue/UPC/Warehouse and Date is in the Exclusion table, shown below.
The tricky part is that data is at the individual day level, but I want to exclude it if it falls within the range.
So here, only I-100/1234/China/5-5-2019 and I-324/1349/NewYork/6-1-2019 should be excluded, since they match values and date ranges in the Exclusion table.
I tried the below query but the SELECT for DATES BETWEEN is returning more than 1 record, and giving me this error Single-row subquery returns more than one row.
update "FactDetails"
set "EXCLUDE" = 'Y'
where ("UPC","Warehouse", "Issue") in
("UPC","Warehouse", "Issue" from "Exclusions"
where "PLANT_NUMBER" is not null
and "ISSUE_CODE" is not null)
and "Date" between
(select "DATE_FROM" from "Exclusion"
where "PLANT_NUMBER" is not null
and "ISSUE_CODE" is not null) and
(select "DATE_TO" from "Exclusion"
where "PLANT_NUMBER" is not null
and "ISSUE_CODE" is not null);
Another complexity is that the Exclusion table can have 'levels' to exclude by combinations of UPC/Issue/Warehouse. Warehouse is the most specific, and if just UPC, this covers the most data.
If I understand correctly, you can use exists
:
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
);