sqlconditional-statementssnowflake-cloud-data-platformansi-sql

SQL: Set conditional value based on varying criteria in another table


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

enter image description here

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.

enter image description here

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

enter image description here

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

Solution

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