sqlconditional-statementssnowflake-cloud-data-platformbetweenansi-sql

SQL: Set conditional value based on another table, with a BETWEEN date criteria


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.

enter image description here

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. enter image description here

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. enter image description here


Solution

  • 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
                     );