Merging Data sets for consecutive months from the source.

[Former Member]

Hi All,

I am trying to design a Data Services job which merges rows from the source and pushes to the target. Customer and Program NM together forms the key field.And if the Start date and end date range for rows are consecutive then we merge it to have the min start date and max end date for the set of rows where the months are consecutive,

Below is my source data set :

Row Customer Program NM Start Date End Date

1 Tina BCBS 09/01/2009 09/30/2009

2 Tina BCBS 10/01/2009 10/31/2009

3 Tina BCBS 11/01/2009 11/30/2009

4 Tina BCBS 01/01/2010 01/31/2010

5 Tina BCBS 04/01/2010 04/30/2010

6 Tina BCBS 05/01/2010 05/31/2010

7 Tina BCBS 11/01/2012 11/30/2012

Target Data should be like below. From the source rows 1,2 and 3 are merged to form one row in the target.And rows 5 and 6 are merged to form a row in the target. The other rows 4 and 7 goes as is.

Customer Program NM Start Date End Date

Tina BCBS 09/01/2009 11/30/2009

Tina BCBS 01/01/2010 01/31/2010

Tina BCBS 04/01/2010 05/31/2010

Tina BCBS 11/01/2012 11/30/2012

Please kindly help me to figure out a design for this scenario.