如何使用Informatica/SQL在两个记录之间的重叠期间创建新记录 [英] How to create a new record for the overlapping period between two records using Informatica/SQL

问题描述

对于以下源数据，需要在重叠期间创建一个新记录，并且其数量应为重叠记录数量的总和.现有记录的开始日期和结束日期也需要更改，以免重叠.

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.

来源:

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

预期输出:

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

如何使用SQL(IBM DB2)/Informatica或两者结合来做到这一点?

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

注意:不能使用存储的procs.

Note : Can't use stored procs.

推荐答案

开始的地方是拆分数据，因此只有一列包含该数量.我认为这会产生您想要的东西:

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;

这篇关于如何使用Informatica/SQL在两个记录之间的重叠期间创建新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！