My recursive query runs with no end in sight. Any idea as to how to solve this?

Question

      With A as(
      select client_id, convert(date,admission_date) start_date, 
      year(convert(date,admission_date))  start_year,
      month(convert(date, admission_date)) start_month
      from ClientEpisode ce
     where episode_status = 'closed'
     and CONVERT(DATE,discharge_date) is not null 
     ------------------
    union all
    --------------------
   select ce.client_id, dateadd(month, 1, start_date),
   year(dateadd(month, 1, start_date)),
   month(dateadd(month, 1, start_date))
  from A rc
  inner join 
  ClientEpisode ce on rc.client_id = ce.client_id
  where dateadd(month, 1, rc.start_date) <= CONVERT(DATE,ce.discharge_date)
  )
 select
 client_id,
 start_year
 from A
 where start_date >= '07/01/22'
 order by client_id asc,start_date asc

Answer 1

Given you recent questions, I think you would really benefit from a Month table of some sort. I have included a demo here of how to construct one and use it in a query to get the answer you want. The reason for your slowness is that the recusive part is going over each Entity+DateRange and it drastically increases the amount of cpu cycles required. If you were able to simplify to just the DateRange in the recursion and THEN join to the entities you want it would be much faster.

But this will be faster still, with no need to recalculate date ranges at all, the data is just stored and ready to access. Add indexes as appropriate, or add additional display methods for your months. As I mentioned in my comment, I have one for each day and another for each year as well.

/** DEFINE dbo.CMonth 
    NOTE - Add Additional indexes as needed.
    **/
DROP TABLE IF EXISTS dbo.CMonth 

CREATE TABLE dbo.CMonth
    (
    CMonthID INT NOT NULL
    , CMonthName NVARCHAR(50) NOT NULL
    , CYear INT NOT NULL
    , CMonth INT NOT NULL
    , CYearMonth NVARCHAR(50) NOT NULL
    , StartDate DATE NOT NULL
    , EndDate DATE NOT NULL
    , CONSTRAINT PK_CMonth PRIMARY KEY CLUSTERED (CMonthID)
    )

CREATE NONCLUSTERED INDEX IDX_CMonth_StartDate_EndDate
    ON dbo.CMonth (StartDate, EndDate)
    WITH (FILLFACTOR=100, SORT_IN_TEMPDB=ON)

GO

/** BUILD dbo.CMonth
    Specify desired start and end date 
    **/
DECLARE @StartDate DATE = '1/1/1950'
DECLARE @EndDate DATe = '12/31/2100'

;WITH CTE_R AS
    (
    SELECT CONVERT(NVARCHAR(10), YEAR(@StartDate)) + RIGHT('00' + CONVERT(NVARCHAR(10), MONTH(@StartDate)), 2) AS CMonthID
        , FORMAT(@StartDate, 'MMMM') AS CMonthName
        , YEAR(@StartDate) AS CYear
        , MONTH(@StartDate) AS CMonth
        , FORMAT(@StartDate, 'yyyy-MM') AS CYearMonth
        , @StartDate AS StartDate
        , EOMONTH(@StartDate) AS EndDate
        , NextMonth = DATEADD(MONTH, 1, @StartDate)
    UNION ALL
    SELECT CONVERT(NVARCHAR(10), YEAR(NextMonth)) + RIGHT('00' + CONVERT(NVARCHAR(10), MONTH(NextMonth)), 2) AS CMonthID
        , FORMAT(NextMonth, 'MMMM') AS CMonthName
        , YEAR(NextMonth) AS CYear
        , MONTH(NextMonth) AS CMonth
        , FORMAT(NextMonth, 'yyyy-MM') AS CYearMonth
        , NextMonth AS StartDate
        , EOMONTH(NextMonth) AS EndDate
        , NextMonth = DATEADD(MONTH, 1, NextMonth)
    FROM CTE_R AS R
    WHERE R.NextMonth <= @EndDate
    )
INSERT INTO dbo.CMonth 
    (CMonthID, CMonthName, CYear, CMonth, CYearMonth, StartDate, EndDate)
SELECT R.CMonthID, R.CMonthName, R.CYear, R.CMonth, R.CYearMonth, R.StartDate, R.EndDate
FROM CTE_R AS R
    LEFT OUTER JOIN dbo.CMonth AS M ON M.CMonthID = R.CMonthID
WHERE M.CMonthID IS NULL
OPTION (MAXRECURSION 0)

GO

/** Display Contents of dbo.CMonth **/
SELECT CMonthID, CMonthName, CYear, CMonth, CYearMonth, StartDate, EndDate 
FROM dbo.CMonth


/** DEMO **/
DECLARE @EntityTest TABLE
    (
    EntityID INT NOT NULL
    , StartDate DATE NOT NULL
    , EndDate DATE NOT NULL
    )

INSERT INTO @EntityTest 
    (EntityID, StartDate, EndDate)
VALUES (1, '5/3/2020', '6/5/2022')
    , (2, '8/3/2023', '9/5/2040')

SELECT E.EntityID
    , M.CYear
    , M.CMonth
    , M.CYearMonth
FROM @EntityTest AS E
    INNER JOIN dbo.CMonth AS M ON M.EndDate >= E.StartDate AND M.StartDate <= E.EndDate
ORDER BY E.EntityID
    , M.CYearMonth