Port test_mutually_exclusive_ranges() to TSQL

[dataders]

my priority for this macro is not the macro itself, but really because it is used in dbt-utils's integration testing for validation of OTHER macros.

calc CTE

these two coalesces are the tricky parts that need to be converted into NULLIFs or CASE WHENs? Also false needs to be replaced with 0 bc TSQL doesn't have a true & false. Here's a gist of the compiled SQL that's error-ing out. If you log into the test db (dbt-msft-serverless-db), you can debug it.

my interpretation for the logic for the first coalesce statement below, though i'm not sure in what scenario lower_bound < upper_bound could be NULL.

• if lower_bound < upper_bound is not null:
  • return the result of the comparison: true or false (or in TSQL's case: 1 or 0)
• else if lower_bound < upper_bound is NULL:
  • return false (i.e. 0)

current error

the error i'm getting is Incorrect syntax near '<'. (102)

Database Error in test dbt_utils_mutually_exclusive_ranges_data_test_mutually_exclusive_ranges_no_gaps_not_allowed__lower_bound__upper_bound (models/schema_tests/schema.yml)
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '<'. (102) (SQLExecDirectW)")
  compiled SQL at target/compiled/dbt_utils_integration_tests/models/schema_tests/schema.yml/schema_test/dbt_utils_mutually_exclusive_ranges_data_test_mutually_exclusive_ranges_no_gaps_f27ec0771ee181e29da8b3adeddba768.sql

-- For each record: lower_bound should be < upper_bound.
-- Coalesce it to return an error on the null case (implicit assumption
-- these columns are not_null)
coalesce(
    lower_bound < upper_bound,
    false
) as lower_bound_less_than_upper_bound,

-- For each record: upper_bound {{ allow_gaps_operator }} the next lower_bound.
-- Coalesce it to handle null cases for the last record.
coalesce(
    upper_bound {{ allow_gaps_operator }} next_lower_bound,
    is_last_record,
    false
) as upper_bound_{{ allow_gaps_operator_in_words }}_next_lower_bound

window_functions CTE

this piece I already was able to convert

row_number() over (
    {{ partition_clause }}
    order by {{ lower_bound_column }} desc
) = 1 as is_last_record

becomes

case when
    row_number() over (
        {{ partition_clause }}
        order by {{ lower_bound_column }} desc
    ) = 1
    then 1 else 0
end as is_last_record