How to create a new record for the overlapping period between two records using Informatica/SQL

Question

For below source data a new record needs to be created for the overlapping period and its the amount should be the sum of the overlapping record's amount. The start date and end date of the existing records also need to be changed so that they do not overlap.

Source :

ID  StartDate EndDate   Amount
1   1-Jan     31-Jul    100
1   1-Jun     31-Dec    100

Expected Output :

ID  StartDate EndDate   Amount
1   1-Jan     31-May    100
1   1-Jun     31-Jul    200
1   1-Aug     31-Dec    100

How can I do this using either SQL(IBM DB2)/Informatica or a combination of both?

Note : Can't use stored procs.

Answer 1

The place to start is by splitting the data so there is only one column with the amount. I think this produces what you want:

select id, dte as StartDate,
       lead(dte) over (partition by id, dte) - 1 day as NextDate,
       sum(sum(amount)) over (partition by id order by dte) as amount
from ((select id, startdate as dte, amount
       from t
      ) union all
      (select id, enddate + 1 day, - amount
       from t
      )
     ) t
group by id, dte;