Date ranges intersections..

Question

MS Sql 2008:

I have 3 tables: meters, transformers (Ti) and voltage transformers (Tu)

ParentId  MeterId  BegDate       EndDate  
10      100      '20050101'    '20060101'

ParentId  TiId     BegDate       EndDate  
10      210      '20050201'    '20050501'
10      220      '20050801'    '20051001'

ParentId  TuId   BegDate       EndDate  
10      300      '20050801'    '20050901'

where date format is yyyyMMdd (year-month-day)

Is there any way to get periods intersection and return the table like this?

ParentId  BegDate     EndDate     MeterId   TiId   TuId    
10      '20050101'  '20050201'  100       null   null 
10      '20050201'  '20050501'  100       210    null 
10      '20050501'  '20050801'  100       null   null 
10      '20050801'  '20050901'  100       220    300 
10      '20050901'  '20051001'  100       220    null
10      '20051001'  '20060101'  100       null   null 

Here is the table creation script:

--meters
declare @meters table 
(ParentId int,
MeterId int,
BegDate smalldatetime,
EndDate smalldatetime
)
insert @meters
select 10, 100, '20050101', '20060101'

--transformers
declare @ti table 
(ParentId int,
TiId int,
BegDate smalldatetime,
EndDate smalldatetime
)
insert @ti
select 10,   210,    '20050201',     '20050501'
union all 
select 10,   220,    '20050801',     '20051001'



--voltage transformers
declare @tu table 
(ParentId int,
TuId int,
BegDate smalldatetime,
EndDate smalldatetime
)

insert @tu
select 10,   300,    '20050801',     '20050901'

Answer 1

This should work:

What's going on here is that I use a CTE (Common Table Expression) to factor out the UNION-based query that gives us all the date points to use to build the intervals.

Once this is done we can use ROW_NUMBER() to give us adjacent pairs of dates for use as intervals, and once we have these it's a simple matter to join in your data appropriately.

Hope this helps!

---

;WITH dates (dval) AS
(
    SELECT DISTINCT begdate AS dval
    FROM @meters m
    UNION
    SELECT DISTINCT enddate AS dval
    FROM @meters m
    UNION
    SELECT DISTINCT begdate AS dval
    FROM @ti ti
    UNION
    SELECT DISTINCT enddate AS dval
    FROM @ti ti
    UNION
    SELECT DISTINCT begdate AS dval
    FROM @tu tu
    UNION
    SELECT DISTINCT enddate AS dval
    FROM @tu tu
)
SELECT m.Parentid, d1.dval AS begdate, d2.dval AS enddate, m.meterid, ti.tiid, tu.tuid
FROM
(
    SELECT dval, ROW_NUMBER() OVER (ORDER BY dval ASC) AS rnum
    FROM dates
) d1
    INNER JOIN 
    (
        SELECT dval, ROW_NUMBER() OVER (ORDER BY dval ASC) AS rnum
        FROM dates
    ) d2 ON d1.rnum+1 = d2.rnum
    LEFT JOIN @meters m ON m.begdate <= d1.dval AND m.enddate >= d2.dval
    LEFT JOIN @ti ti ON ti.begdate <= d1.dval AND ti.enddate >= d2.dval
    LEFT JOIN @tu tu ON tu.begdate <= d1.dval AND tu.enddate >= d2.dval

Answer 2

mwigdahl,
Great thanks! I've just modified your solution to use with ParentId in case when meters table will contain rows with differents parentId, for sample:

ParentId  MeterId  BegDate       EndDate  
10      100      '20050101'    '20060101'
20      110      '20050201'    '20050701'

Here is the script

    --meters
DECLARE @Meters
TABLE   (
        ParentId    INTEGER,
        MeterId     INTEGER,
        BegDate     SMALLDATETIME,
        EndDate     SMALLDATETIME
        )

--transformers
DECLARE @TI
TABLE   (
        ParentId    INTEGER,
        TiId        INTEGER,
        BegDate     SMALLDATETIME,
        EndDate     SMALLDATETIME
        )

--voltage transformers
DECLARE @TU
TABLE   (
        ParentId    INTEGER,
        TuId        INTEGER,
        BegDate     SMALLDATETIME,
        EndDate     SMALLDATETIME
        )

INSERT  @Meters (ParentId, MeterId, BegDate, EndDate)
SELECT  10, 100, '20050101', '20060101'
UNION   ALL 
SELECT  20, 110, '20050201', '20050701'

INSERT  @TI (ParentId, TiId, BegDate, EndDate)
SELECT  10, 210, '20050201', '20050501'
UNION   ALL 
SELECT  10, 220, '20050801', '20051001'
UNION   ALL 
SELECT  20, 230, '20050101', '20050301'
UNION   ALL 
SELECT  20, 240, '20050501', '20051001'



INSERT  @TU (ParentId, TuId, BegDate, EndDate)
SELECT  10, 300, '20050801', '20050901'
UNION   ALL 
SELECT  20, 310, '20050101', '20050601'


;with dM (ParentId, MeterId) as
(
select distinct ParentId, MeterId from @meters
),
dates (ParentId, meterid, dval) AS
(
    SELECT ParentId, meterid, begdate AS dval
    FROM @meters m
    UNION
    SELECT ParentId, meterid, enddate AS dval
    FROM @meters m
    UNION
    SELECT ti.ParentId, meterid, begdate AS dval
    FROM @ti ti
    join dM dm on ti.ParentId = dm.ParentId
    UNION
    SELECT ti.ParentId, meterid, enddate AS dval
    FROM @ti ti
    join dM dm on ti.ParentId = dm.ParentId
    UNION
    SELECT tu.ParentId, meterid, begdate AS dval
    FROM @tu tu
    join dM dm on tu.ParentId = dm.ParentId
    UNION
    SELECT tu.ParentId, meterid, enddate AS dval
    FROM @tu tu
    join dM dm on tu.ParentId = dm.ParentId
)
select m.ParentId, d1.dval AS begdate, d2.dval AS enddate, m.MeterId, TiId, TuId
FROM
(
    SELECT ParentId, meterid, dval, ROW_NUMBER() OVER (ORDER BY ParentId asc, meterid ASC, dval ASC) AS rnum
    FROM dates
) d1
    INNER JOIN 
    (
        SELECT ParentId, meterid, dval, ROW_NUMBER() OVER (ORDER BY ParentId asc, meterid ASC, dval ASC) AS rnum
        FROM dates
    ) d2 ON d1.ParentId = d2.ParentId and d1.meterid = d2.meterid and d1.rnum+1 = d2.rnum
    LEFT JOIN @meters m ON m.ParentId = d1.ParentId and m.ParentId = d2.ParentId and m.meterid = d1.meterid and m.meterid = d2.meterid and m.begdate <= d1.dval AND m.enddate >= d2.dval
    LEFT JOIN @ti ti ON ti.ParentId = d1.ParentId and ti.ParentId = d2.ParentId and ti.begdate <= d1.dval AND ti.enddate >= d2.dval
    LEFT JOIN @tu tu ON tu.ParentId = d1.ParentId and tu.ParentId = d2.ParentId and tu.begdate <= d1.dval AND tu.enddate >= d2.dval
    where not (m.ParentId is null) and not (m.meterid is null)
    order by d1.ParentId, d1.MeterId, d1.dval, d2.dval
;