0

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

user14464843
  • 1
  • 1
  • Hi - what is the logic you are using to determine the correct order of the records? Why is Chennai 3rd in the list rather than 1st or 2nd? Thanks – NickW Oct 16 '20 at 20:47
  • Please tag your question with the database you are running: mysql, oracle, sqlserver...? – GMB Oct 16 '20 at 20:50
  • Hi NickW, i edited the question for more details, when i am loading the same data through informatica chennai record is there in source( source data is daily snapshot) . so when i inserted it, it came as first record in current_table (loading from informatica) – user14464843 Oct 16 '20 at 21:12
  • Red shift is the DB – user14464843 Oct 16 '20 at 21:13
  • Why does Hyderabad show Chennai time? – June7 Oct 16 '20 at 21:17
  • @user14464843 . . . Do you know 100% that there are no gaps in the times for a given id in both tables? – Gordon Linoff Oct 16 '20 at 21:20
  • this is sample , there can be gaps, as i told you those from_date and to_date are driven by ETL – user14464843 Oct 16 '20 at 23:39
  • @June7 please ignore timestamp part, its coming from ETL server on the day process run (sysdate). as i said there is no from_date or to_date coming from source – user14464843 Oct 16 '20 at 23:43
  • Hi - based on the data you have provided, all you seem to be doing is changing the to_date of the last record in the history table, the from_date in the first record of the current table and resetting the version numbers. If this is the case then why not just copy the data into a temp table, do a manual update and write it back to the current table? If the real world data is more complicated than this then you will need to provide the logic that has to be followed to merge these 2 datasets i.e. how do i know which is record 1, which is the next record, what dates do i give it, etc. – NickW Oct 18 '20 at 17:28

0 Answers0