# all appointment based on interval distance

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)

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

• Mind adding some justification as to why this performs better? Commented Sep 22, 2020 at 21:32
• @Dannnno it doesn't involve querying any tables, doesn't create, populate or pull from any #temp tables. Doesn't create any unnecessary rows and therefore does not need to be filtered and there is no sorting required. Is that sufficient justification? Commented Sep 22, 2020 at 21:37
• It is - can you add it to the question? Other thoughts: 1. using a variable with TOP leads to an automatic cardinality estimate of 100 rows, which may have negative consequences depending on usage (RECOMPILE hint or dynamic SQL addresses this) 2. Enabling batch mode (fake join or query hint, depending on SQL Server version) is just a hair better, but probably not enough to matter 3. Including query plans to demonstrate the improvement is always nice :) brentozar.com/pastetheplan/?id=BkfrSxdSv Commented Sep 22, 2020 at 22:06
• 1) This is true but I've never seen it cause a problem, not even when working with millions of rows. 2) I'm not familiar with what you're doing here but the paste a plan doesn't show the creation of the #CciForBatchMode temp table (so I don't know how you have it defined). To test, I simply created it with a single BIT column and 0 rows (just to make the code execute). In my test, #CciForBatchMode was ignored and both plans were identical in shape, subtree cost and performance. That said, I am interested in learning more about the technique. Please post links if you have any available. Commented Sep 22, 2020 at 22:49
• 3) Execution plans are a useful tool but they aren't a substitute for an actual performance test. If I'm really trying to eek out every last millisecond, I'll post execution times and IO stats before execution plans (just my own preference). Commented Sep 22, 2020 at 22:50

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
UNION ALL
SELECT
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
UNION ALL
SELECT
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)