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!)
I have a Date Dimension table in which all the related Calendar Conversion is stored like
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)