====== Crontab ======

This article provides an implementation of a ''[[https://en.wikipedia.org/wiki/Cron#Overview|crontab]]'' file's time/date specification expression in T-SQL. [[https://github.com/atifaziz/NCrontab/|NCrontab]] is a great (and better!) choice for this if you can use a .NET library, but if you can't do so inside SQL Server (maybe your DBAs don't like CLR functions, maybe you're [[https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-transact-sql-information#transact-sql-syntax-not-supported-in-azure-sql-database|in Azure]]) then this may be of use.

It has two component functions:
  * ''tvf_GetNextOccurrence'' takes a [[https://github.com/atifaziz/NCrontab/wiki/Crontab-Expression|crontab expression]] and returns a corresponding list of ''DATETIME'' values;
  * ''tvf_CrontabFieldValues'' is a helper function that parses individual date/time component fields into corresponding value lists.

===== tvf_GetNextOccurrence =====
Returns a list of ''DATETIME'' values corresponding to a ''crontab'' expression. Parameters:
  * ''@after'' -- lower bound for values returned;
  * ''@crontabExpr'' -- the ''crontab'' expression;
  * ''@limit'' -- the maximum number of occurrences to return.
Passing these parameters into ''tvf_GetNextOccurrence'' returns a list of the first ''@limit'' dates & times indicated by ''@crontabExpr'' after ''@after''. 

<code tsql>/*
 * Return the next few start date/times indicated by a crontab expression
 * (c) Richard Swinbank 2019-2020
 * http://richardswinbank.net
 */
CREATE FUNCTION [dbo].[tvf_GetNextOccurrence] (
  @after DATETIME
, @crontabExpr VARCHAR(255)
, @limit INT = 1
) RETURNS TABLE
AS RETURN
 
WITH fields AS (
  -- split incoming expression into its five fields
  SELECT
    SUBSTRING(
      @crontabExpr
    , LAG(N, 1, 0) OVER (ORDER BY N) + 1
    , N - LAG(N, 1, 0) OVER (ORDER BY N) - 1
    ) AS FieldValue
  , CASE ROW_NUMBER() OVER (ORDER BY N)
      WHEN 1 THEN 'MINUTES'
      WHEN 2 THEN 'HOURS'
      WHEN 3 THEN 'DAYS'
      WHEN 4 THEN 'MONTHS'
      WHEN 5 THEN 'DAYS-OF-WEEK'
    END AS FieldName
  FROM dbo.TinyIntegers n
  WHERE SUBSTRING(REPLACE(@crontabExpr, ' ', 'x') + 'x', N, 1) = 'x'  
    -- really we're looking for spaces, but turn them into 'x' because T-SQL doesn't always play nicely with them
  AND LEN(@crontabExpr) - LEN(REPLACE(@crontabExpr, ' ', '')) = 4  -- refuse to handle badly-formatted expressions
), [values] AS (
  -- expand the list of fields into a list of corresponding field values
  SELECT
    f.FieldName
  , tvf.N AS [Value]
  FROM fields f
    CROSS APPLY tvf_CrontabFieldValues (FieldValue, FieldName) tvf
)
SELECT TOP (@limit)
  validated.[ScheduledDateTime] AS NextOccurrence
FROM (
  SELECT [Value] AS [Minute]  -- list of minutes expanded from @crontabExpr
  FROM [values] WHERE FieldName = 'MINUTES'
) m CROSS JOIN (
  SELECT [Value] AS [Hour]  -- list of hours expanded from @crontabExpr
  FROM [values] WHERE FieldName = 'HOURS'
) h CROSS JOIN (
  SELECT [Value] AS [DayOfMonth]  -- list of days-of-month expanded from @crontabExpr
  FROM [values] WHERE FieldName = 'DAYS'
) d CROSS JOIN (
  SELECT [Value] AS [Month]  -- list of months expanded from @crontabExpr
  FROM [values] WHERE FieldName = 'MONTHS'
) mth CROSS JOIN (
  SELECT [Value] AS [DayOfWeek]  -- list of days-of-week expanded from @crontabExpr
  FROM [values] WHERE FieldName = 'DAYS-OF-WEEK'
) w CROSS APPLY (
  SELECT DATEADD(SECOND, -(DATEPART(SECOND, @after)), DATEADD(MILLISECOND, -(DATEPART(MILLISECOND, @after)), @after)) AS StartMinute
) cutoff CROSS APPLY ( 
  -- Field value lists are CROSS JOINed above to generate all matching date patterns.
  -- Here we translate the patterns into dates, but use the 1st of the month instead
  -- of the day from the pattern (because we don't know at this point if the month
  -- has a day of that number; e.g. there is no February 30th).
 
  -- generate a date during this year
  SELECT
    DATETIMEFROMPARTS(
      DATEPART(YEAR, StartMinute)
    , mth.[Month]
    , 1
    , h.[Hour]
    , m.[Minute]
    , 0, 0
    ) AS [ScheduledDateTime]
    , d.[DayOfMonth]
 
  UNION ALL
 
  -- generate a date during next year (in case we're nearly there)
  SELECT
    DATETIMEFROMPARTS(
      DATEPART(YEAR, StartMinute) + 1
    , mth.[Month]
    , 1
    , h.[Hour]
    , m.[Minute]
    , 0, 0
    ) AS [ScheduledDateTime]
    , d.[DayOfMonth]
 
) proto CROSS APPLY (
  -- determine whether the each month has a day of the number from the pattern, and 
  -- if so build it (this protects us from invalid DATETIMEFROMPARTS construction)
  SELECT 
    CASE
      WHEN DATEPART(MONTH, proto.[ScheduledDateTime] - 1 + proto.[DayOfMonth]) = DATEPART(MONTH, proto.[ScheduledDateTime])
        THEN proto.[ScheduledDateTime] - 1 + proto.[DayOfMonth]
    END AS [ScheduledDateTime]
) validated
WHERE DATEPART(WEEKDAY, validated.[ScheduledDateTime]) = w.[DayOfWeek] + 1  -- crontab weekdays from 0
  -- note that where day-of-month & weekday are both specified, this returns only dates matching *both* criteria
AND validated.[ScheduledDateTime] >= StartMinute  -- (also eliminates NULLs introduced by [validated])
ORDER BY NextOccurrence
</code>

===== tvf_CrontabFieldValues =====
{{tag> tsql}}

Helper function for ''tvf_GetNextOccurrence''. Could do a better job at excluding badly-formatted fields -- this implementation accepts anything that simply contains no invalid characters. For example, ''1*'' in the ''HOURS'' field is accepted but expands (counterintuitively) to ''10-23'' (''1'', followed by ''*'' expanded to ''0-23''). 

<code tsql>/*
 * Translate a single crontab field into a corresponding list of values
 * (c) Richard Swinbank 2019-2020
 * http://richardswinbank.net
 */
CREATE   FUNCTION [dbo].[tvf_CrontabFieldValues] (
  @fieldValue NVARCHAR(255)
, @crontabField VARCHAR(25)
) RETURNS TABLE
AS RETURN
 
WITH ranges AS (
  -- translate value ranges into min/max pairs
  SELECT 
    TRY_CAST(IIF(CHARINDEX('-', rm.[Range]) > 0, LEFT(rm.[Range], CHARINDEX('-', rm.[Range]) - 1), rm.[Range]) AS INT) AS MinValue
  , TRY_CAST(IIF(CHARINDEX('-', rm.[Range]) > 0, RIGHT(rm.[Range], LEN(rm.[Range]) - CHARINDEX('-', rm.[Range])), rm.[Range]) AS INT) AS MaxValue
  , TRY_CAST([Modulo] AS INT) AS [Modulo]
  FROM (
    -- split the "every nth iteration" value off (if any)
    SELECT
      IIF(CHARINDEX('/', c.FieldPart) > 0, LEFT(c.FieldPart, CHARINDEX('/', c.FieldPart) - 1), c.FieldPart) AS [Range]
    , IIF(CHARINDEX('/', c.FieldPart) > 0, RIGHT(c.FieldPart, LEN(c.FieldPart) - CHARINDEX('/', c.FieldPart)), NULL) AS [Modulo]
    FROM (
      -- parse a comma-separated list into separate components
      SELECT
        REPLACE(
          SUBSTRING(
            @fieldValue
          , LAG(N, 1, 0) OVER (ORDER BY N) + 1
          , N - LAG(N, 1, 0) OVER (ORDER BY N) - 1
          )
        , '*', '0-59') AS FieldPart
      FROM dbo.TinyIntegers
      WHERE SUBSTRING(@fieldValue + ',', N, 1) = ','
    ) c
  ) rm
)
SELECT 
  DISTINCT n.N
FROM ranges r
  INNER JOIN (
    -- generate a list of all possible integers (0-59 is the largest range) 
    SELECT TOP 60 N - 1 AS N
    FROM dbo.TinyIntegers
  ) n ON n.N BETWEEN r.MinValue AND r.MaxValue
WHERE (r.[Modulo] IS NULL OR n.N % r.Modulo = 0)  -- run every r.[Modulo] iterations
AND n.N BETWEEN IIF(@crontabField IN ('DAYS', 'MONTHS'), 1, 0) AND
  CASE @crontabField
    WHEN 'MINUTES' THEN 59
    WHEN 'HOURS' THEN 23
    WHEN 'DAYS' THEN 31
    WHEN 'MONTHS' THEN 12
    WHEN 'DAYS-OF-WEEK' THEN 6
  END  -- run during intervals
AND @fieldValue NOT LIKE '%[^0-9,\-/*]%' ESCAPE '\'  -- refuse badly-formatted fields (mostly...)
AND @fieldValue NOT LIKE '[\-/,]%' ESCAPE '\'
AND @fieldValue NOT LIKE '%[\-/,]' ESCAPE '\'
</code>

===== What is dbo.TinyIntegers? =====

The code here makes use of a table called ''dbo.TinyIntegers''. This is a **small** [[numbers_table|numbers table]]:
  * column [N] is of type TINYINT
  * it contains integers from **1** to 255 (no zero!)

<code tsql>CREATE TABLE dbo.TinyIntegers (
  N TINYINT PRIMARY KEY
)</code>