all appointment based on interval distance

Question

Currently with a given Date and an Interval in Day, I can calculate all of the appointment in the future. for example if you are supposed to have an appointment each 30 day starting '2020-09-24', all the future appointments would be calculated like this.

--Source Table Creation
DROP TABLE IF EXISTS #DimDate

CREATE TABLE #DimDate ( id INT PRIMARY KEY IDENTITY, GregorianDate DATE)
--Sample Data
INSERT INTO #DimDate(GregorianDate)
SELECT  TOP 2000 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) , GETDATE())
FROM    sys.all_objects

--Calculation Code


;WITH cte_name --30.4375
AS ( SELECT GregorianDate,DATEADD(DAY,30,GregorianDate) next30, ((ROW_NUMBER() OVER (ORDER BY GregorianDate ASC)-1) + 30) % 30 AS r
     FROM   #DimDate
     WHERE GregorianDate >='2020-09-24'
)
SELECT  *
FROM    cte_name
WHERE r=0

Calculation is based on residual of this part the code ((ROW_NUMBER() OVER (ORDER BY GregorianDate ASC)-1) + 30) % 30 AS r, if residual is 0 then we are at the next interval.

What I am looking for

• Is there any problem not yet considered?
• Is there any other way? ( some playful way like window aggregate!)

Thank you

---

Update:

Some clarification:

I have a Date Dimension table in which all the related Calendar Conversion is stored like HijriCalendar, PersianCalendar and other needed data.

That is why even though generally speaking calculating nth appointment using (n * 30 (interval distance) ) + start is faster but overall because of other data needed from Dimension it'd be the same. (Greatly appreciated)

Answer 1

You can greatly simplify things as well as wildly improve performance by using something like the following...

DECLARE 
    @start_date date = '2020-09-24',
    @interval_in_days int = 30,
    @appt_count int = 100;

WITH 
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),   -- 10
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),                             -- 100
    cte_Calendar (dt) AS (
        SELECT TOP (@appt_count)
            DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1) * @interval_in_days, @start_date)
        FROM
            cte_n2 a CROSS JOIN cte_n2 b                                                    -- 10,000
        )
SELECT 
    c.dt
FROM
    cte_Calendar c;

You can even even create an inline table function to make life a little easier...

SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

CREATE FUNCTION dbo.tfn_CalculateAppointmentDates
/* ===================================================================
09/22/2020, Created: Calculates all future appointment dates based 
                on a start date, specified interval and number of 
                requested appointments. 
=================================================================== */
--===== Define I/O parameters
(
    @start_date date,
    @interval_in_days int,
    @appt_count int 
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
    WITH 
        cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),   -- 10
        cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),                             -- 100
        cte_Calendar (dt) AS (
            SELECT TOP (@appt_count)
                DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1) * @interval_in_days, @start_date)
            FROM
                cte_n2 a CROSS JOIN cte_n2 b                                                    -- 10,000
            )
    SELECT 
        c.dt
    FROM
        cte_Calendar c;
GO

Answer 2

There is no need to query sys.all_objects, a simple CTE recursion would be enough:

DECLARE 
    @START_DATE DATE = '2020-09-24'
,   @END_DATE   DATE = '2026-04-26'

SET @START_DATE = DATEADD(DAY, 30 , @START_DATE) 

;WITH CTE AS
(
  SELECT
    DATEADD(DAY, 0, @START_DATE) GregorianDate
,   DATEADD(DAY, 30, @START_DATE) next30
  UNION ALL 
  SELECT 
    DATEADD(DAY, 30, GregorianDate)
,   DATEADD(DAY, 30, next30)
  FROM CTE 
  WHERE DATEADD(DAY, 30, next30) <= @END_DATE
)
SELECT * FROM CTE 
OPTION(maxrecursion 0)

now, you can wrap this up into a Table-valued Function to make it reusable :

CREATE FUNCTION GetAllAppointmentsByIntervalDistance
(   
    @START_DATE DATE
,   @END_DATE DATE
,   @DAYS INT 
)
RETURNS @TABLE TABLE(GregorianDate DATE, next30 DATE)
AS
BEGIN 

    SET @START_DATE = DATEADD(DAY, @DAYS , @START_DATE) 

    ;WITH CTE AS
    (
      SELECT
        DATEADD(DAY, 0 , @START_DATE) GregorianDate
    ,   DATEADD(DAY, @DAYS, @START_DATE) next30
      UNION ALL 
      SELECT 
        DATEADD(DAY, @DAYS, GregorianDate)
    ,   DATEADD(DAY, @DAYS, next30)
      FROM CTE 
      WHERE DATEADD(DAY, @DAYS, next30) <= @END_DATE
    )
    INSERT INTO @TABLE(GregorianDate , next30)
    SELECT * FROM CTE 
    OPTION(maxrecursion 0)
    
    RETURN 
END

Then, reuse it :

DECLARE 
    @START_DATE DATE = '2020-09-24'
,   @END_DATE   DATE = '2026-04-26'
,   @DAYS       INT  = 30

SELECT * 
FROM 
    dbo.GetAllAppointmentsByIntervalDistance(@START_DATE, @END_DATE, @DAYS)