All geek questions in one place

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 row per year.

DateFrom and dateTo can be on any day of the year, but when the dates are separated, we assume that the line ends on December 31, and the start of a new line on January 1.

Here is the data view

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

Expected Result: (each split by range)

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 using a recursive CTE based on this: Possible recursive CTE query using date ranges But I cannot achieve the desired result.

I made this request:

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

thanks for reference

+4
source share
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

+2

Source: https://habr.com/ru/post/1608711/

More articles:


All Articles