Julian Julian - 3 months ago 5
SQL Question

merging child-table date range with parent

I asked a similar question in the past, and imagine there is already an answer for this, but I can't seem to figure out the wording to locate it.

I have a parent table with a date range and a child table that can have multiple date ranges within the date range of the parent table. I need to merge them into a row for each record in the series. An example should better explain what I'm trying:

Table 1 (Parent)

Date1 Date2 Person
1/1/16 7/1/16 A

Table 2 (Child)

Date1 Date2 Person
2/1/16 2/4/16 B
3/6/16 3/8/16 C
5/4/16 5/9/16 B

I want a merged table like so:

Merged Table

Date1 Date2 Person
1/1/16 2/1/16 A
2/1/16 2/4/16 B
2/4/16 3/6/16 A
3/6/16 3/8/16 C
3/8/16 5/4/16 A
5/4/16 5/9/16 B
5/9/16 7/1/16 A

There must be a somewhat easy way to do this? I'm fine with a complicated while loop, but am stumped on the logic for this too.


Perhaps something like this?

Declare @Table1 table (Date1 Date,Date2 Date, Person varchar(25))
Insert into @Table1 values 

Declare @Table2 table (Date1 Date,Date2 Date, Person varchar(25))
Insert into @Table2 values 

;with cteBase as (
    Select *
          ,Gap1 = Date2
          ,Gap2 = Lead(Date1,1,(Select max(Date2) from @Table1)) over (Order by Date1)
     From  @Table2 
Select Date1,Date2=(Select min(Date1) from @Table2),Person From @Table1
Union All
Select Date1,Date2,Person from cteBase
Union All
Select Date1=Gap1,Date2=Gap2,Person=B.Person
 From  cteBase A
 Join  @Table1 B on 1=1 
Order by Date1


Date1       Date2       Person
2016-01-01  2016-02-01  A
2016-02-01  2016-02-04  B
2016-02-04  2016-03-06  A
2016-03-06  2016-03-08  C
2016-03-08  2016-05-04  A
2016-05-04  2016-05-09  B
2016-05-09  2016-07-01  A