3
\$\begingroup\$

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)

\$\endgroup\$
3
\$\begingroup\$

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
\$\endgroup\$
7
  • \$\begingroup\$ Mind adding some justification as to why this performs better? \$\endgroup\$ – Dan Oberlam Sep 22 '20 at 21:32
  • \$\begingroup\$ @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? \$\endgroup\$ – Jason A. Long Sep 22 '20 at 21:37
  • 1
    \$\begingroup\$ 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 \$\endgroup\$ – Dan Oberlam Sep 22 '20 at 22:06
  • \$\begingroup\$ 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. \$\endgroup\$ – Jason A. Long Sep 22 '20 at 22:49
  • \$\begingroup\$ 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). \$\endgroup\$ – Jason A. Long Sep 22 '20 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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