-1
      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
1
  • 3
    Given your recent questions, it seems like you would really benefit from having a calendar table at whatever granularity you feel benefits you best. We have one for Days, Weeks, Months and Years. Just populate it and then be done with it. Then you can join to it and easily get your results. Aug 15 at 14:01

1 Answer 1

3

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
1
  • Thank you at @Jonathan Fite Aug 30 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.