SQL to UNION history table with current table (with date range aligned)

185 views Asked by At

Hi i have below 2 tables

table_histroy

version,   from_date             to_date                 ID   Place 
1       1900-01-01 00:00:00     2020-07-08 10:00:49     123    Delhi
2       2020-07-08 10:00:49     2199-12-31 23:59:59     123    hyderabad

table_current

version      from_date             to_date                ID     Place 
1       1900-01-01 00:00:00  2020-10-07 18:03:56        123    Chennai
2       2020-10-07 18:03:56  2020-10-09 12:28:30        123    Kolkata
3       2020-10-09 12:28:30  2199-12-31 23:59:59        123    mumbai

table_history is loaded by some etl process (Datastage) table_current is loaded by different etl proceess (informatica)

Datastage is going shut off soon, data load should be continued with Informatica

i need to do union of above tables ( table_history is already is prod) in such way that date ranges columns align between history and current table with version numbers accordingly as below

union view(expected)

version from_date to_date ID Place

1       1900-01-01 00:00:00     2020-07-08 10:00:49         123    Delhi
2       2020-07-08 10:00:49     2020-10-07 18:03:56         123    hyderabad
3       2020-10-07 18:03:56     2020-10-07 18:03:56         123    Chennai
4       2020-10-07 18:03:56     2020-10-09 12:28:30         123    Kolkata
5       2020-10-09 12:28:30     2199-12-31 23:59:59         123    mumbai

help is appreciated

Notes: from_date and to_date are driven from ETL process -not coming from source

I am trying with windows functions but not getting desired result

lag(sysdate) over (partition by id order by sysdate asc) is null 
         then TIMEZONE('GMT', to_timestamp('1900-01-01 00:00:00', 'yyyy-mm-dd'))         else sysdate
      end as from_date
    , nvl(lead(sysdate) over (partition by id order by stg_load_dttm asc), TIMEZONE('GMT', to_timestamp('2199-12-31 23:59:59', 'yyyy-mm-dd HH24:MI:SS'))) as to_date

above is the logic to determine the from_date, to_date in SQL which is building table_current

0

There are 0 answers