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'
