CODEWITHSUNDEEP
sql-server
totalitarian
totalitarian

Reputation: 3676

SQL Server - Generating schedule list of repeating dates

We have a set of records where each record (Job) has a cycle (or frequency) in days that determines when the job appears on somebody's work list. E.G

Job A is generated every 7 days Job B is generated every 14 days.

Each Job also has a start date.

I need to generate a table which contains all the possible job dates between a date range.

Here is my first part of code which generates the possible dates.

DECLARE @ForecastEarliestStartDate as DATETIME, @formStartDate as DATE,@formEndDate as DATE, @cycleInDays as BIGINT;

--set input param values
SET @ForecastEarliestStartDate = CAST('2017-07-03' AS DATETIME)
SET @formStartDate = getdate();
SET @formEndDate = getdate()+60;
SET @cycleInDays = 28;


WITH mycte AS
(
  SELECT @ForecastEarliestStartDate DateValue
  UNION ALL
  SELECT   DateValue + @cycleInDays
  FROM    mycte   
  WHERE   DateValue + @cycleInDays < @formEndDate
)

SELECT  *
FROM    mycte
WHERE DateValue between @formStartDate and @formEndDate
OPTION (MAXRECURSION 0);

This works fine as for a single Job, but how can I run it against a whole table of Jobs where @ForecastEarliestStartDate, @formStartDate, @formEndDate, @cycleInDays are fields in a table?

Upvotes: 2

Views: 1052

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82020

I'll often use a TVF to create dynamic date/time ranges. Often faster than a recursive CTE and it is parameter driven. You supply the date/time range, datepart, and increment.

For Example

Select * From [dbo].[udf-Range-Date]('2017-07-03',getdate()+60,'DD',7)

Returns

RetSeq  RetVal
1   2017-07-03
2   2017-07-10
3   2017-07-17
4   2017-07-24
5   2017-07-31
6   2017-08-07
7   2017-08-14
8   2017-08-21
9   2017-08-28

The UDF if Interested

CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
    with cte0(M)   As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
         cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N)   As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ),
         cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 )

    Select RetSeq = N+1
          ,RetVal = D 
     From  cte3,cte0 
     Where D<=@R2
)
/*
Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
Syntax:
Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1) 
Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1) 
*/

Edit - Working Example of CROSS APPLY

Declare @YourTable table (JobName varchar(50),StartDate date,Interval int)
Insert Into @YourTable values
 ('Job A','2017-07-03',7)
,('Job B','2017-07-03',14)


Select A.JobName
      ,JobDate = B.RetVal
 From @YourTable A
 Cross Apply [dbo].[udf-Range-Date](A.StartDate,getdate()+60,'DD',A.Interval) B

Returns

JobName JobDate
Job A   2017-07-03
Job A   2017-07-10
Job A   2017-07-17
Job A   2017-07-24
Job A   2017-07-31
Job A   2017-08-07
Job A   2017-08-14
Job A   2017-08-21
Job A   2017-08-28
Job B   2017-07-03   -- << Notice differant span for Job B
Job B   2017-07-17
Job B   2017-07-31
Job B   2017-08-14
Job B   2017-08-28

Upvotes: 3

GarethD
GarethD

Reputation: 69819

The first thing to note is that a recursive CTE is one of the worst ways to generate a set or series (the worst being with explicit loops). Before going any further I would recommend reading the following series of articles:

For example's sake, I will assume that you don't have a numbers table, and can't create one, so I will use the stacked CTE method. This query will get you a list of numbers from 0 to 99,999. 

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2)
SELECT N = ROW_NUMBER() OVER(ORDER  BY N) - 1
FROM N3

If you need more numbers, you can add cross joins, if you need less you can remove them.

You can then just join these numbers to your table of jobs, each time adding (n * CycleInDays) to your start date:

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Numbers (Number) AS (SELECT N = ROW_NUMBER() OVER(ORDER  BY N) - 1 FROM N3)

SELECT  t.*,
        Iteration = n.Number,
        Date = DATEADD(DAY, n.Number * t.cycleInDays, t.ForecastEarliestStartDate)
FROM    (VALUES
            (1, CONVERT(DATE, '2017-07-03'), CONVERT(DATE, '2017-07-03'), CONVERT(DATE, '2017-09-03'), 28 ),
            (2, CONVERT(DATE, '2017-07-03'), CONVERT(DATE, '2017-07-03'), CONVERT(DATE, '2017-09-03'), 19)
        ) t (JobID, ForecastEarliestStartDate, formStartDate, formEndDate, cycleInDays)
        INNER JOIN Numbers AS N
            ON t.formEndDate >= DATEADD(DAY, n.Number * t.cycleInDays, t.ForecastEarliestStartDate)
            AND t.formStartDate <= DATEADD(DAY, n.Number * t.cycleInDays, t.ForecastEarliestStartDate)
ORDER BY t.JobID, n.Number;

This gives:

JobID   ForecastEarliestStartDate   formStartDate   formEndDate     cycleInDays     Iteration   Date
------------------------------------------------------------------------------------------------------------------
1       2017-07-03                  2017-07-03      2017-09-03      28              0           2017-07-03
1       2017-07-03                  2017-07-03      2017-09-03      28              1           2017-07-31
1       2017-07-03                  2017-07-03      2017-09-03      28              2           2017-08-28
2       2017-07-03                  2017-07-03      2017-09-03      19              0           2017-07-03
2       2017-07-03                  2017-07-03      2017-09-03      19              1           2017-07-22
2       2017-07-03                  2017-07-03      2017-09-03      19              2           2017-08-10
2       2017-07-03                  2017-07-03      2017-09-03      19              3           2017-08-29

ADDENDUM

In response to the comments:

Great. So the general idea is that a loop is slower but if you genuinely don't know how man iterations there could be, what then? A loop?

No, as long as your numbers table is big enough to cover the maximum iterations then you don't need a loop. The example I have used with 100,000 rows is enough to cover 273 years of jobs that run every single day. I would have thought this would suffice.

BTW I do have a numbers table. Could you show me how to solve my problem without the need generating it please

Sure, simply remove the CTE that generates the numbers, and change the reference to the Numbers CTE to whatever your numbers table is called:

SELECT  t.*,
        Iteration = n.Number,
        Date = DATEADD(DAY, n.Number * t.cycleInDays, t.ForecastEarliestStartDate)
FROM    (VALUES
            (1, CONVERT(DATE, '2017-07-03'), CONVERT(DATE, '2017-07-03'), CONVERT(DATE, '2017-09-03'), 28 ),
            (2, CONVERT(DATE, '2017-07-03'), CONVERT(DATE, '2017-07-03'), CONVERT(DATE, '2017-09-03'), 19)
        ) t (JobID, ForecastEarliestStartDate, formStartDate, formEndDate, cycleInDays)
        INNER JOIN dbo.Numbers AS N  ---- CHANGE TO WHATEVER YOUR NUMBERS TABLE IS CALLED
            ON t.formEndDate >= DATEADD(DAY, n.Number * t.cycleInDays, t.ForecastEarliestStartDate)
            AND t.formStartDate <= DATEADD(DAY, n.Number * t.cycleInDays, t.ForecastEarliestStartDate)
ORDER BY t.JobID, n.Number;

Just noticed you are hard coding the Jobs into the query. They exist in another table, so not sure how this solves my issue

I have hard coded the jobs into the query just to emulate the table that you have (I have had to guess a bit since there is not much information about this table in the question). Simply replace the table-value constructor I have used with your actual table. e.g.

SELECT  t.*,
        Iteration = n.Number,
        Date = DATEADD(DAY, n.Number * t.cycleInDays, t.ForecastEarliestStartDate)
FROM    dbo.[Your Job Table] AS t
        INNER JOIN dbo.[Your Numbers Table] AS N 
            ON t.formEndDate >= DATEADD(DAY, n.Number * t.cycleInDays, t.ForecastEarliestStartDate)
            AND t.formStartDate <= DATEADD(DAY, n.Number * t.cycleInDays, t.ForecastEarliestStartDate)
ORDER BY t.JobID, n.Number;

Upvotes: 2

Related Questions