ORACLE - Test if the SCD type 2 start and end rows are consecutive

ORACLE_test_SCD_type2_dates.sql

-- We test if the SCD type 2 start and end rows are consecutive

-- NOTE: in this case the last end date is always the day before the start of the next SCD register, 
-- but that seems bad practice as it wouldn't work naturally with BETWEEN startDate and endDate.

-- returns 1 (ok) when there is no prev (or next) record to test for that group (partition)
SELECT 
 id, colA, period_start_date, period_end_date 
, CASE WHEN ( last_end is null OR last_end = period_start_date - 1 ) THEN 1 else 0 END as start_date_OK
, CASE WHEN ( next_start is null OR next_start = period_end_date + 1 ) THEN 1 else 0 END as end_date_OK
-- for testing
--, last_end
--, next_start
-- we truncate dates because we do not care about time of day
FROM (
    select 
      id, colA
    , trunc(period_start_date) as period_start_date
    , trunc(period_end_date) as period_end_date
    , LAG (trunc(period_end_date)) over (partition by id order by colA, period_start_date) as last_end
    , LEAD (trunc(period_start_date)) over (partition by id order by colA, period_start_date) as next_start
    from SCD2Test 
) 
ORDER BY colA, period_start_date;
;

-- #TODO: create test for the "active" flag (must be always only the last of each group by ordered by start date).

-- #TODO: create test for the "version" column (must be incremental ordered by start date, maybe not necessarily consecutive?).