CODEWITHSUNDEEP
sqloracle-databasedb2
user4396022
user4396022

Reputation: 43

Merge Date ranges between two tables in oracle or db2

I have two tables with tenure and rate data for an employee as below:

1) Tenure_T 

  E_Id         Start_Dt      End_Dt

    1          1-Jan-2013    31-Dec-2013
    1          1-Jan-2014    30-Jun-2014
    1          1-Jul-2014    31-Jul-2014
    1          1-Aug-2014    31-Dec-2014

2) Rate_T

E_Id      Rt_Strt_Dt  Rt_End_Dt   Amt

1         1-Jun-2013  30-Nov-2013 100
1         1-Dec-2013  31-Jan-2014 200
1         1-Feb-2014  31-Mar-2014 300
1         1-Apr-2014  31-Jul-2014 400
1         1-Aug-2014  31-Jan-2015 500

I want to join these tables and need output like this as below:

3) Emp_T

E_Id Start_Dt      End_Dt                 Amt

1                 1-Jan-2013 31-May-2013  0
1                 1-Jun-2013 30-Nov-2013  100
1                 1-Dec-2013 31-Dec-2013  200
1                 1-Jan-2014  31-Jan-2014 200
1                 1-Feb-2014 31-Mar-2014  300
1                 1-Apr-2014  31-Jul-2014 400
1                 1-Aug-2014 31-Dec-2014  500
1                 1-Jan-2014 31-Jan-2015  500

So wherever there is an overlap of dates start break it with appropriate Amt through sqls.Data volume is not much so multiple queries/multiple scans can be written/done but solution should be dynamic.

Upvotes: 4

Views: 189

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do this by first arranging the data based only on start dates and then re-aggregating using the lead() function. I think the following does what you want:

select t.e_id, t.start_dte,
       lead(t.start_dte) over (partition by t.e_id order by t.start_dte) as end_dte,
       max(amt)
from ((select e_id, start_dt, 0 as amt
       from tenure_t
      ) union all
      (select e_id, rt_start_dte, amt
       from rate_t
      )
     ) t
group by t.e_id, t.start_dte;

Upvotes: 2

Related Questions