Separating date ranges into separate segments in SQL
I have a table containing 2 date fields and an identifier开发者_如何学C (id, fromdate and todate) These dates overlap in any and every possible way. I need to produce a list of segments each with a start and end date describing the separate segments in that list.
For example:
id, FromDate ToDate
1, 1944-12-11, 1944-12-31
2, 1945-01-01, 1945-12-31
3, 1945-01-01, 1945-06-30
4, 1945-12-31, 1946-05-01
5, 1944-12-17, 1946-03-30
Should produce all the segments of all the overlaps:
1, 1944-12-11, 1944-12-16
1, 1944-12-17, 1944-12-31
5, 1944-12-17, 1944-12-31
2, 1945-01-01, 1945-06-30
3, 1945-01-01, 1945-06-30
5, 1945-01-01, 1945-06-30
2, 1945-07-01, 1945-12-09
5, 1945-07-01, 1945-12-09
2, 1945-12-10, 1945-12-31
4, 1945-12-10, 1945-12-31
5, 1945-12-10, 1945-12-31
4, 1946-01-01, 1946-03-30
5, 1946-01-01, 1946-03-30
4, 1946-04-01, 1946-05-01
Or perhaps a diagram might help
INPUT
1 <---->
2 <----------->
3 <----->
4 <---------->
5 <----------------->
OUTPUT
1 <->
1 <->
5 <->
2 <----->
3 <----->
5 <----->
2 <->
5 <->
2 <->
4 <->
5 <->
4 <->
5 <->
4 <---->
Please help
You can use this SO question's answer as a basis.
It splits into this output but I think it's easy to morph into what you need:
OUTPUT
1 <->
1,5 <->
2,3,5 <----->
...
This link should also prove helpful - it merely shows how to build the ranges.
try this below script
SCHEMA:
CREATE TABLE splitDates
([member] int, effdt datetime, termdt datetime)
;
INSERT INTO splitDates
([member], effdt, termdt)
VALUES
(1, '1944-12-11', '1944-12-31'),
(2, '1945-01-01', '1945-12-31'),
(3, '1945-01-01', '1945-06-30'),
(4, '1945-12-31', '1946-05-01'),
(5, '1944-12-17', '1946-03-30');
SQL SCRIPT:
DECLARE @SplitRanges TABLE ([member] int, theDate datetime, des varchar(10));
INSERT INTO @SplitRanges
SELECT DISTINCT
m.member,
memDt.theDate,
memdt.des
FROM splitDates m
JOIN ( SELECT DISTINCT effdt as theDate,'effdt' des FROM splitDates
union all
SELECT DISTINCT effdt-1 as theDate,'trmdt' des FROM splitDates
union all
SELECT DISTINCT termdt as theDate,'trmdt' des FROM splitDates
union all
SELECT DISTINCT termdt+1 as theDate,'effdt' des FROM splitDates
) as memDt on 1=1
WHERE memDt.theDate >= m.effdt
AND memDt.theDate <= m.termdt
ORDER BY m.member, memdt.thedate;
;WITH rowNumbers as (
SELECT ef.member, ef.thedate,ef.des,
ROW_NUMBER() over (PARTITION BY ef.member ORDER BY ef.thedate ASC) AS rn
FROM @SplitRanges ef
)
SELECT DISTINCT ef.member, ef.thedate as effdt, tm.thedate as termdt FROM rowNumbers ef
JOIN rownumbers tm on tm.member=ef.member and ef.rn+1=tm.rn
WHERE ef.des='effdt'
order by ef.thedate
OUTPUT:
member effdt termdt
1 1944-12-11 00:00:00.000 1944-12-16 00:00:00.000
1 1944-12-17 00:00:00.000 1944-12-31 00:00:00.000
5 1944-12-17 00:00:00.000 1944-12-31 00:00:00.000
2 1945-01-01 00:00:00.000 1945-06-30 00:00:00.000
3 1945-01-01 00:00:00.000 1945-06-30 00:00:00.000
5 1945-01-01 00:00:00.000 1945-06-30 00:00:00.000
2 1945-07-01 00:00:00.000 1945-12-30 00:00:00.000
5 1945-07-01 00:00:00.000 1945-12-30 00:00:00.000
2 1945-12-31 00:00:00.000 1945-12-31 00:00:00.000
4 1945-12-31 00:00:00.000 1945-12-31 00:00:00.000
5 1945-12-31 00:00:00.000 1945-12-31 00:00:00.000
4 1946-01-01 00:00:00.000 1946-03-30 00:00:00.000
5 1946-01-01 00:00:00.000 1946-03-30 00:00:00.000
4 1946-03-31 00:00:00.000 1946-05-01 00:00:00.000
精彩评论