开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜