March 16, 2010 at 8:01 am
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'
March 16, 2010 at 12:31 pm
There are a myriad of assumptions and the result is not cleaned for combining like ranges for result.
: script uses numbers table used to create date range from beg/end-date
;WITH cteMonths AS (
SELECT MIN(begDate) begMonth, MAX(endDate) endMonth,
0 begMonthCount,
(MONTH(MAX(endDate)) + YEAR(max(endDate)) * 12 )
- (MONTH(MIN(begDate)) + YEAR(MIN(begDate)) * 12) EndMonthCount
FROM
(SELECT MIN(begDate) begDate, MAX(endDate) endDate FROM @meters UNION
SELECT MIN(begDate), MAX(endDate) FROM @ti UNION
SELECT MIN(begDate), MAX(endDate) FROM @tu) myMinDate
),
cteParent AS (
SELECT DISTINCT parentID FROM @meters UNION
SELECT DISTINCT parentID FROM @ti UNION
SELECT DISTINCT parentID FROM @tu
),
cteMonthList AS (
SELECT DATEADD(m, n.Number -1, begMonth) myMonth
FROM cteMonths m
INNER JOIN dbo.Numbers n ON n.Number -1 BETWEEN m.begMonthCount AND m.EndMonthCount
)
SELECT p.*, ml.*, m.MeterId, ti.TiId, tu.TuId
FROM cteParent p
CROSS JOIN cteMonthList ml
LEFT JOIN @meters m ON p.parentID = m.parentID
AND ml.myMonth BETWEEN m.BegDate AND m.EndDate
LEFT JOIN @ti ti ON p.parentID = ti.parentID
AND ml.myMonth BETWEEN ti.BegDate AND ti.EndDate
LEFT JOIN @tu tu ON p.parentID = tu.parentID
AND ml.myMonth BETWEEN tu.BegDate AND tu.EndDate
ORDER BY 2
*****************
parentIDmyMonthMeterIdTiIdTuId
102005-01-01 00:00:00100NULLNULL
102005-02-01 00:00:00100210NULL
102005-03-01 00:00:00100210NULL
102005-04-01 00:00:00100210NULL
102005-05-01 00:00:00100210NULL
102005-06-01 00:00:00100NULLNULL
102005-07-01 00:00:00100NULLNULL
102005-08-01 00:00:00100220300
102005-09-01 00:00:00100220300
102005-10-01 00:00:00100220NULL
102005-11-01 00:00:00100NULLNULL
102005-12-01 00:00:00100NULLNULL
102006-01-01 00:00:00100NULLNULL
March 17, 2010 at 3:41 am
Setup:
--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';
INSERT @TI (ParentId, TiId, BegDate, EndDate)
SELECT 10, 210, '20050201', '20050501'
UNION ALL
SELECT 10, 220, '20050801', '20051001';
INSERT @TU (ParentId, TuId, BegDate, EndDate)
SELECT 10, 300, '20050801', '20050901';
Solution:
WITH Dates
AS (
SELECT DISTINCT Value
FROM (
SELECT BegDate, EndDate
FROM @TI
UNION ALL
SELECT BegDate, EndDate
FROM @TU
UNION ALL
SELECT BegDate, EndDate
FROM @Meters
) Dates
UNPIVOT (
Value FOR
Name IN (BegDate, EndDate)
) U
),
OrderedDates
AS (
SELECT n = ROW_NUMBER() OVER (ORDER BY Value),
Value
FROM Dates
),
Ranges
AS (
SELECT from_date = This.Value,
to_date = Preceding.Value
FROM OrderedDates This
JOIN OrderedDates Preceding
ON This.n = Preceding.n - 1
)
SELECT M.ParentId, R.from_date, R.to_date, M.MeterId, TI.TiId, TU.TuId
FROM @Meters M
CROSS
JOIN Ranges R
OUTER
APPLY (
SELECT TI.TiId
FROM @TI TI
WHERE TI.ParentId = M.ParentId
AND TI.BegDate < R.to_date
AND TI.EndDate > R.from_date
) TI
OUTER
APPLY (
SELECT TU.TuId
FROM @TU TU
WHERE TU.ParentId = M.ParentId
AND TU.BegDate < R.to_date
AND TU.EndDate > R.from_date
) TU;
March 17, 2010 at 9:05 am
Paul,
Slick bit of sql.
Specifically the range table with only the relevant ranges and the Outer Apply. Thanks for the couple new tools in the tool belt.
Daryl
March 17, 2010 at 9:44 am
Daryl-273852 (3/17/2010)
Slick bit of sql. Specifically the range table with only the relevant ranges and the Outer Apply. Thanks for the couple new tools in the tool belt.
Thank you very much, Daryl!
March 18, 2010 at 12:03 am
Paul,
Thank you for reply. I will try your solution.
March 18, 2010 at 12:09 am
bramanthe (3/18/2010)
Thank you for reply. I will try your solution.
Let us know how you get on.
March 19, 2010 at 12:47 am
Paul,
here is the script I'm going to use for my application. I've just added some additional rows to @meters, @ti and @tu tables with different ParentId.
--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
;
March 19, 2010 at 4:27 am
bramanthe (3/19/2010)
here is the script I'm going to use for my application. I've just added some additional rows to @meters, @ti and @tu tables with different ParentId.
Are you asking me to fix it?
March 19, 2010 at 4:55 am
Paul,
no. I've just shown you my solution as the answer for
Paul White NZ (3/18/2010)
Let us know how you get on.
March 19, 2010 at 5:31 am
bramanthe (3/19/2010)
Paul,no. I've just shown you my solution as the answer for
Paul White NZ (3/18/2010)
Let us know how you get on.
Oh right. I see. It doesn't have an UNPIVOT, and all the unions are UNION rather than UNION ALL, that's what made me wonder.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply