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