Possible Recursive Cte Query Using Date Ranges

Possible recursive CTE query using date ranges

You don't need a "real" recursive CTE here. You can use one for the month references though:

;WITH Months
as
(
    SELECT DATEADD(day, -DATEPART(day, GETDATE())+1, GETDATE()) as 'MonthStart'
    UNION ALL
    SELECT DATEADD(month, -1, MonthStart) as 'MonthStart'
    FROM Months
)

Then you can JOIN to SELECT TOP 13 * FROM Months in your above query.

I'm not going to try to parse all your CASE statements, but essentially you can use a GROUP BY on the date and the MonthStart fields, like:

GROUP BY Datepart(year, monthstart), Datepart(month, monthstart)

and aggregate by month. It will probably be easiest to have all your options (active, lapsed, etc) as columns and calculate each with a SUM(CASE WHEN ... THEN 1 ELSE 0 END) as it will be easier with a GROUP BY.

Recursive CTE to split date range

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

How to create recursive query to get all dates between two dates

You need recursive CTE:

DECLARE  @arrival_date date = '2016-01-01',
         @leaving_date date = '2016-02-01'

;WITH cte AS (
SELECT @arrival_date as date_
UNION ALL
SELECT CAST(DATEADD(day,1,date_) as date)
FROM cte
WHERE date_ < @leaving_date
)

SELECT *
FROM cte
OPTION (MAXRECURSION 0)

Output:

date_
2016-01-01
2016-01-02
2016-01-03
...
2016-01-30
2016-01-31
2016-02-01

EDIT1

Based on your sample:

;WITH cte AS (
SELECT GuestID, CAST(ArrivalDate as date) as date_
FROM Guests
UNION ALL
SELECT c.GuestID, CAST(DATEADD(day,1,date_) as date)
FROM cte c
INNER JOIN Guests g
    ON g.GuestID = c.GuestID
WHERE date_ < g.LeavingDate
)

SELECT *
FROM cte
ORDER BY GuestID, date_
OPTION (MAXRECURSION 0)

EDIT2

;WITH Guests AS (
SELECT  1 as GuestID,
        '2016-01-01' ArrivalDate,
        '2016-01-05' LeavingDate
UNION ALL
SELECT  2 ,
        '2016-06-17',
        '2016-06-20'
), cte AS (
SELECT GuestID, CAST(ArrivalDate as date) as date_
FROM Guests
UNION ALL
SELECT c.GuestID, CAST(DATEADD(day,1,date_) as date)
FROM cte c
INNER JOIN Guests g
    ON g.GuestID = c.GuestID
WHERE date_ < g.LeavingDate
)

SELECT *
FROM cte
ORDER BY GuestID, date_
OPTION (MAXRECURSION 0)

Output:

GuestID date_
1   2016-01-01
1   2016-01-02
1   2016-01-03
1   2016-01-04
1   2016-01-05
2   2016-06-17
2   2016-06-18
2   2016-06-19
2   2016-06-20

Create a recursive list of dates given from two Different Dates and items

You can try to use CTE recursion to make it.

The keypoint is, let startTime column add one day then convert DateTime to Date in the CTE recursion condition.

CREATE TABLE T(
  ID INT,
  [Start Date Time] DATETIME,
  [End Date Time] DATETIME
);
INSERT INTO T VALUES (1,'2018-08-06 13:00:00.000','2018-08-07 10:00:00.000');
INSERT INTO T VALUES (2,'2018-08-10 08:00:00.000','2018-08-10 15:00:00.000');

Query 1:

;WITH CTE AS (
    SELECT ID,[Start Date Time] startTime,[End Date Time] endTime
    FROM T
    UNION ALL
    SELECT ID,CAST(CAST(DATEADD(DAY,1,startTime)AS DATE) AS DATETIME),endTime 
    FROM CTE
    WHERE CAST(CAST(DATEADD(DAY,1,startTime) AS DATE) AS DATETIME) < endTime
)
SELECT id,
       startTime AS 'Start Date Time',
       (CASE WHEN LEAD(startTime,1,endTime) OVER(PARTITION BY ID ORDER BY startTime)= endTime 
                 THEN endTime
                 ELSE DATEADD(second,-1,LEAD(startTime,1,endTime) OVER(PARTITION BY ID ORDER BY startTime)) 
            END) AS 'End Date Time' 
FROM CTE

Results:

| id |      Start Date Time |        End Date Time |
|----|----------------------|----------------------|
|  1 | 2018-08-06T13:00:00Z | 2018-08-06T23:59:59Z |
|  1 | 2018-08-07T00:00:00Z | 2018-08-07T10:00:00Z |
|  2 | 2018-08-10T08:00:00Z | 2018-08-10T15:00:00Z |

Recursive CTE Returns Value Outside of Range

Because your Recursive CTE add one day from fromDate

Select
     fromDate = dateadd(day, 1, @EndDate)

but your condition filter fromDate

where
     fromDate >= @StartDate AND
     fromDate < @EndDate ))

---

If you want to do make calendar table by Recursive CTE.

you can try this.

with Dates as (
  select @StartDate fromDate,@EndDate endDate
  UNION ALL
  Select
     fromDate = dateadd(day, 1, fromDate),endDate
   from
     Dates
   where
      dateadd(day, 1, fromDate) <= @EndDate 
)
Select * from Dates
OPTION (MAXRECURSION 0);

---

Related Topics