Possible recursive CTE query using date ranges

Question

Not sure how to even phrase the title on this one!

I have the following data:

IF OBJECT_ID ('tempdb..#data') IS NOT NULL DROP TABLE #data
CREATE TABLE #data
(
id UNIQUEIDENTIFIER
,reference NVARCHAR(30)
,start_date DATETIME
,end_date DATETIME
,lapse_date DATETIME
,value_received DECIMAL(18,3)
)

INSERT INTO #data VALUES ('BE91B9C1-C02F-46F7-9B63-4D0B25D9BA2F','168780','2006-05-01 00:00:00.000',NULL,'2011-09-27 00:00:00.000',537.42)
INSERT INTO #data VALUES ('B538F123-C839-447A-B300-5D16EACF4560','320858','2011-08-08 00:00:00.000',NULL,NULL,0)
INSERT INTO #data VALUES ('1922465D-2A55-434D-BAAA-8E15D681CF12','306597','2011-04-08 00:00:00.000','2011-06-22 13:14:40.083','2011-08-07 00:00:00.000',12)
INSERT INTO #data VALUES ('7DF8FBCC-B490-4892-BDC5-8FD2D73B0323','321461','2011-07-01 00:00:00.000',NULL,'2011-09-25 00:00:00.000',8.44)
INSERT INTO #data VALUES ('1EC2E754-F325-4313-BDFC-9010E255F6FE','74215','2000-10-31 00:00:00.000',NULL,'2011-08-30 00:00:00.000',258)
INSERT INTO #data VALUES ('9E59B09C-0198-48AC-8EEC-A0D76CEA9385','169194','2008-06-25 00:00:00.000',NULL,'2011-09-25 00:00:00.000',1766.4)
INSERT INTO #data VALUES ('97CF6C0F-324A-49A6-B9D8-AC848A1F821A','288039','2010-09-01 00:00:00.000','2011-07-29 00:00:00.000','2011-08-21 00:00:00.000',55)
INSERT INTO #data VALUES ('97CF6C0F-324A-49A6-B9D8-AC848A1F821A','324423','2011-08-01 00:00:00.000',NULL,'2011-09-25 00:00:00.000',5)
INSERT INTO #data VALUES ('D5E5197A-E8E1-468C-9991-C8712224C2BF','323395','2011-08-25 00:00:00.000',NULL,NULL,0)
INSERT INTO #data VALUES ('0EC4976C-16B9-4C99-BD07-D0CBDF014D32','323741','2011-08-25 00:00:00.000',NULL,NULL,0)

And I want to be able to group all references into a category of 'active', 'lapsed' or 'new' based upon the following criteria:

• Active has a start date that is less than the last date of the reference month, a lapse date after the last day of the prior month and a value_received > 0;

• New has a start date which falls within the reference month;

• Lapsed has a lapse date which falls within the reference month.

And to then apply these definitions for each reference for a rolling 13 months (so from Now going back as far as July 2010) so that for each month I can see how many references fall into each group.

I am able to use the following to define this for the current month:

select 
id
,reference
,start_date
,end_date
,lapse_date 
,value_received
,CASE   WHEN start_date < DATEADD(month,DATEPART(Month,GETDATE()) + 1,DATEADD(year,DATEPART(year,GETDATE())-1900,0)) --next month start date
        AND lapse_date > DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)) --last day of current month
        AND value_received > 0
        THEN 'Active'
        WHEN lapse_date < DATEADD(month,DATEPART(Month,GETDATE()) + 1,DATEADD(year,DATEPART(year,GETDATE())-1900,0)) --next month start
            AND lapse_date > DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) --last day of prior month
        THEN 'lapse'
        WHEN start_date < DATEADD(month,DATEPART(Month,GETDATE()) + 1,DATEADD(year,DATEPART(year,GETDATE())-1900,0)) --next month start date
        AND start_date > DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) --last day of prior month
        THEN 'New'
        ELSE 'Not applicable'
 END AS [type]
from #data

But I can't see a nice / efficient way of doing this (other than to repeat this query 13 times and union the results, which I know is just awful)

Would this be a case for using the current month as an anchor and using recursion (if so, some pointers would be most appreciated)?

Any help most appreciated as always :)

* Edited to include actual solution *

In case it's of interest to anyone, this is the final query I used:

;WITH Months as 
(
SELECT DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)) as month_end
,0 AS level
UNION ALL     
SELECT DATEADD(month, -1, month_end)as month_end
,level + 1 FROM Months
WHERE level < 13 
) 
SELECT 
DATENAME(Month,month_end) + ' ' + DATENAME(YEAR,month_end) as date
,SUM(CASE WHEN start_date <= month_end
        AND Month(start_date) <> MONTH(Month_end)
        AND lapse_date > Month_end 
 THEN 1 ELSE 0 END) AS Active
,SUM(CASE WHEN start_date <= Month_end 
        AND DATENAME(MONTH,start_date) + ' ' + DATENAME(YEAR,start_date) = 
        DATENAME(MONTH,month_end) + ' ' + DATENAME(YEAR,month_end)
THEN 1 ELSE 0 END) AS New
,SUM(CASE WHEN lapse_date <= Month_end 
        AND Month(lapse_date) = MONTH(Month_end)
THEN 1 ELSE 0 END) AS lapse
FROM #data
CROSS JOIN Months
WHERE id IS NOT NULL
AND start_date IS NOT NULL
GROUP BY DATENAME(Month,month_end)  + ' ' + DATENAME(YEAR,month_end) 
ORDER by MAX(level) ASC

Answer 1

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.

Answer 2

You can cross join your request with a recursive CTE, this is a good idea.

WITH thirteenMonthBack(myDate, level) as
(
   SELECT GETDATE() as myDate, 0 as level
   UNION ALL
   SELECT DATEADD(month, -1, myDate), level + 1
   FROM thirteenMonthBack
   WHERE level < 13
)
SELECT xxx
FROM youQuery
   CROSS JOIN thirteenMonthBack

Answer 3

DECLARE @date DATE = GETDATE()

;WITH MonthsCTE AS (
    SELECT 1 [Month], DATEADD(DAY, -DATEPART(DAY, @date)+1, @date) as 'MonthStart'
    UNION ALL
    SELECT [Month] + 1, DATEADD(MONTH, 1, MonthStart)
    FROM MonthsCTE 
    WHERE [Month] < 12 )

SELECT * FROM MonthsCTE

Answer 4

/*
 | The below SELECT statements show TWO examples of how this can be useful.  
 | Example 1 SELECT: Simple example of showing how to generate 12 days ahead based on date entered
 | Example 2 SELECT: This example shows how to generate 12 months ahead based on date entered
 |  This example tries to mimic as best it can Oracles use of LEVEL and CONNECT BY LEVEL
*/
WITH dynamicRecords(myDate, level) AS
(
   SELECT GETDATE() AS myDate, 1 AS level
   
   UNION ALL
   
   SELECT myDate + 1, level + 1          /* 12 Days - WHERE level < 12  */
   --SELECT DATEADD(month, 1, myDate), level + 1 /* 12 Months - WHERE level < 12 */
   FROM dynamicRecords
   WHERE level < 12
)
SELECT *
FROM dynamicRecords
Option  (MaxRecursion 0) /* The default MaxRecursion setting is 100. Generating more than 100 dates using this method will require the Option (MaxRecursion N) segment of the query, where N is the desired MaxRecursion setting. Setting this to 0 will remove the MaxRecursion limitation altogether */

Screenshots:

/* Original T-SQL Solution I found here: https://riptutorial.com/sql-server/example/11098/generating-date-range-with-recursive-cte
 | The below provides an example of how to generate the days within a date range of the dates entered.
 | The below SELECT statements show TWO examples of how this can be useful.  
 | Example 1 SELECT: Uses static dates to display ALL of the dates within the range for the dates entered
 | Example 2 SELECT: This example uses GETDATE() and then obtains the FOM day and the EOM day of the dates
 |              beging entered to then show all days in the month of the dates entered.
*/
With DateCte AS
(
    SELECT CAST('2021-04-21' AS DATE) AS BeginDate, CAST('2022-05-02' AS DATE) AS EndDate
    --SELECT CAST( GETDATE() - Day(GETDATE()) + 1 AS DATE ) AS BeginDate, CAST(EOMONTH(GETDATE()) AS DATE) AS EndDate
    UNION ALL
    SELECT  DateAdd(Day, 1, BeginDate), EndDate
    FROM    DateCte
    WHERE   BeginDate < EndDate
)
Select  BeginDate AS Dates
From    DateCte
Option  (MaxRecursion 0) /* The default MaxRecursion setting is 100. Generating more than 100 dates using this method will require the Option (MaxRecursion N) segment of the query, where N is the desired MaxRecursion setting. Setting this to 0 will remove the MaxRecursion limitation altogether */
;

Screenshot: