Tetris SQL

This is a DB2 solution. Oracle will be different for the 'daterange' WITH clause (subquery factoring)

-- Two step process:
-- Delete rows in Target that don't exist in Results, 
-- Insert rows from Result that don't exist in Target.

-- Tricky bit: 
-- Don't overwrite any manually added rows in the Target.
-- Instead block *around* them; Tetris.


-- DELETE
delete from TARGET_TBL d
where d.source = 'ee'
and 
    (processing_range.start_dt between d.start_dt and d.end_dt
    or processing_range.end_dt between d.start_dt and d.end_dt
    or d.start_dt between processing_range.start_dt and processing_range.end_dt)
and not exists
    (select 1
    from RESULTS_TBL a
    where a.pin_num = d.pin_num
    and a.start_dt = d.start_Dt
    and a.end_dt = d.end_dt)



-- INSERT
with daterange(level,dt) as (
    select 1, current date - 1 year
    from sysibm.sysdummy1
    union all select level + 1, dt + 1 day
    from daterange
    where level < 1000 and dt < current date + 1 year
)

select '1. Result' as thing, pin_num, start_dt, end_dt, 'ee' as source from RESULTS_TBL
union
select '2. Existing' as thing, pin_num, start_dt, end_dt, source from TARGET_TBL

union
select 
  '3. Answer' as thing
, a.pin_num

-- For PI/EA
, greatest(a.start_dt, 
    (select coalesce(max(s.end_dt) + 1 day, a.start_dt)
    from TARGET_TBL s
    where s.pin_num = a.pin_num
    and s.end_dt between a.start_dt and d.dt)) as start_dt

-- For PI/EA
, least(a.end_dt, 
    (select coalesce(min(s.start_dt) - 1 day, a.end_dt)
    from TARGET_TBL s
    where s.pin_num = a.pin_num
    and s.start_dt between d.dt and s.end_dt)) as end_dt

, 'ee' as source
from 
  RESULTS_TBL a
, daterange d
where d.dt between a.start_dt and a.end_dt
and not exists
    (select 1
    from TARGET_TBL x
    where x.pin_num = a.pin_num
    and d.dt between x.start_dt and x.end_dt)