sql-server - 递归 CTE 来分割日期范围

Question

我需要从成员资格表中提取和拆分数据。我想分割范围以每年获取一行。

DateFrom 和 dateTo 可以是一年中的任何一天，但当日期被分割时，我们假设一行于 12 月 31 日结束，新行于 1 月 1 日开始

这是数据的外观

membershipId - groupId - ClientId - DateFrom - DateTo
2707    20008   1579    1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
20989   20008   1579    1999-01-01 00:00:00.000 2004-12-31 00:00:00.000
39874   20298   1579    2005-01-01 00:00:00.000 2008-12-31 00:00:00.000
50295   21661   1579    2009-01-01 00:00:00.000 2009-12-31 00:00:00.000
50988   20399   1579    2010-01-01 00:00:00.000 2010-12-31 00:00:00.000
52378   21661   1579    2011-01-01 00:00:00.000 2013-12-31 00:00:00.000
57274   21660   1579    2014-01-01 00:00:00.000 3000-01-01 00:00:00.000

预期结果是:(每个范围分割)

2707    20008   1579    1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
20989   20008   1579    1999-01-01 00:00:00.000 1999-12-31 00:00:00.000
20989   20008   1579    2000-01-01 00:00:00.000 2000-12-31 00:00:00.000
20989   20008   1579    2001-01-01 00:00:00.000 2001-12-31 00:00:00.000
20989   20008   1579    2002-01-01 00:00:00.000 2002-12-31 00:00:00.000
20989   20008   1579    2003-01-01 00:00:00.000 2003-12-31 00:00:00.000
20989   20008   1579    2004-01-01 00:00:00.000 2004-12-31 00:00:00.000
50295   21661   1579    2009-01-01 00:00:00.000 2009-12-31 00:00:00.000
50988   20399   1579    2010-01-01 00:00:00.000 2010-12-31 00:00:00.000
52378   21661   1579    2011-01-01 00:00:00.000 2011-12-31 00:00:00.000
52378   21661   1579    2012-01-01 00:00:00.000 2012-12-31 00:00:00.000
52378   21661   1579    2013-01-01 00:00:00.000 2013-12-31 00:00:00.000
57274   21660   1579    2014-01-01 00:00:00.000 3000-01-01 00:00:00.000

我尝试基于此使用递归 CTE: Possible recursive CTE query using date ranges但我无法达到预期的结果。

我提出了这个查询:

WITH splitDates(startDate,endDate, newDate,client, groupingId ) as
(
   SELECT m.datefrom as  startDate, m.dateTo
   , CASE 
   when year(m.dateFrom) <> year(m.dateto) then CAST(CAST(year(m.dateFrom) AS varchar) + '-' + CAST(12 AS varchar) + '-' + CAST(31 AS varchar) AS DATETIME)
    else m.dateTo
    end
   , m.legalEntityId, m.groupingId
   from adesse.dbo.membership m
   UNION ALL
   SELECT DATEADD(year, 1, startDate), 
   CAST(CAST(year(startDate)+1 AS varchar) + '-' + CAST(12 AS varchar) + '-'    + CAST(31 AS varchar) AS DATETIME)
   ,CAST(CAST(year(startDate)+1 AS varchar) + '-' + CAST(12 AS varchar) + '- ' + CAST(31 AS varchar) AS DATETIME)
   ,client, groupingId
   FROM splitDates
   WHERE year(startDate) <> year(endDate)
)
SELECT *
FROM  splitDates
where client  =  1579
order by startDate

但是结果不完整:(

startDate   endDate newDate client  groupingId
1997-01-01 00:00:00.000 1997-12-31 00:00:00.000 1997-12-31 00:00:00.000 1579    20008
1999-01-01 00:00:00.000 2004-12-31 00:00:00.000 1999-12-31 00:00:00.000 1579    20008
2000-01-01 00:00:00.000 2000-12-31 00:00:00.000 2000-12-31 00:00:00.000 1579    20008
2005-01-01 00:00:00.000 2008-12-31 00:00:00.000 2005-12-31 00:00:00.000 1579    20298
2006-01-01 00:00:00.000 2006-12-31 00:00:00.000 2006-12-31 00:00:00.000 1579    20298
2009-01-01 00:00:00.000 2009-12-31 00:00:00.000 2009-12-31 00:00:00.000 1579    21661
2010-01-01 00:00:00.000 2010-12-31 00:00:00.000 2010-12-31 00:00:00.000 1579    20399
2011-01-01 00:00:00.000 2013-12-31 00:00:00.000 2011-12-31 00:00:00.000 1579    21661
2012-01-01 00:00:00.000 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000 1579    21661
2014-01-01 00:00:00.000 3000-01-01 00:00:00.000 2014-12-31 00:00:00.000 1579    21660
2015-01-01 00:00:00.000 2015-12-31 00:00:00.000 2015-12-31 00:00:00.000 1579    21660

感谢帮助

Answer 1

我不确定您的最后日期是否为 3000-01-01，但这应该可行

CREATE TABLE members (membershipId INT, groupId INT, clientId INT, dateFrom DATETIME, dateTo DATETIME)
INSERT INTO members VALUES 
(2707,    20008,   1579,    '1997-01-01 00:00:00.000', '1997-12-31 00:00:00.000'),
(20989,   20008,   1579,    '1999-01-01 00:00:00.000', '2004-12-31 00:00:00.000'),
(39874,   20298,   1579,    '2005-01-01 00:00:00.000', '2008-12-31 00:00:00.000'),
(50295,   21661,   1579,    '2009-01-01 00:00:00.000', '2009-12-31 00:00:00.000'),
(50988,   20399,   1579,    '2010-01-01 00:00:00.000', '2010-12-31 00:00:00.000'),
(52378,   21661,   1579,    '2011-01-01 00:00:00.000', '2013-12-31 00:00:00.000'),
(57274,   21660,   1579,    '2014-01-01 00:00:00.000', '3000-01-01 00:00:00.000')

;

WITH cte AS 
(
    SELECT 
        membershipId,
        groupId,
        clientId,
        dateFrom,
        DATEADD(day, -1, DATEADD(YEAR,1,dateFrom)) newDateTo,
        dateTo 
    FROM 
        members
    UNION ALL
        SELECT 
            m.membershipId,
            m.groupId,
            m.clientId,
            DATEADD(YEAR,1,c.dateFrom),
            DATEADD(day, -1, DATEADD(YEAR,2,c.dateFrom)),
            c.dateto
        FROM 
            members m
            JOIN cte c ON  c.membershipId = m.membershipId
                           AND DATEADD(YEAR,1,c.dateFrom) < m.dateTo
)
SELECT  
    membershipId,
    groupId,
    clientId,
    dateFrom,
    newDateTo dateTo
FROM 
    cte
ORDER BY 
    membershipId, dateFrom
OPTION (MAXRECURSION 0);

DROP TABLE members

SQL Fiddle