CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expression
Vincent Tassin
Vincent Tassin

Reputation: 43

Recursive CTE to split date range

I need to extract and split data from a membership table. I want to split the range to get one line per year.

DateFrom and dateTo can be any day of the year but when dates are split, we assume that a row ends on december 31 and a new row start on january 1st

Here's a look of the data

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

The expected result is : (every range split) 

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

I tried to use recursive CTE based on this : Possible recursive CTE query using date ranges But I cannot achieve the desired result.

I made this query : 

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

But the result is incomplete :(

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

Thx for the help

Upvotes: 4

Views: 1901

Answers (1)

JamieD77
JamieD77

Reputation: 13949

I'm not sure if your last date is suppose to be 3000-01-01 but this should work

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

Upvotes: 2

Related Questions