Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Last revision Both sides next revision
tsql:crontab [2019/10/22 22:42]
admin created
tsql:crontab [2019/10/22 22:55]
admin
Line 48: Line 48:
   ) n   ) n
   WHERE SUBSTRING(REPLACE(@crontabExpr,​ ' ', '​x'​) + '​x',​ N, 1) = '​x'  ​   WHERE SUBSTRING(REPLACE(@crontabExpr,​ ' ', '​x'​) + '​x',​ N, 1) = '​x'  ​
-    -- really we're looking for spaces, but turn them into '​x'​ because T-SQL is a bit unhelpful+    -- 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   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) SELECT TOP (@limit)
   validated.[ScheduledDateTime] AS NextOccurrence   validated.[ScheduledDateTime] AS NextOccurrence
 FROM ( FROM (
-  SELECT ​ +  SELECT ​[Value] ​AS [Minute] ​ -- list of minutes ​expanded from @crontabExpr 
-    tvf.N AS [Minute] ​ -- translate MINUTES field into a list of minutes +  FROM [values] ​WHERE FieldName = '​MINUTES'​
-  FROM fields f +
-    CROSS APPLY tvf_CrontabFieldValues (FieldValue,​ FieldName) tvf +
-  ​WHERE f.FieldName = '​MINUTES'​+
 ) m CROSS JOIN ( ) m CROSS JOIN (
-  SELECT ​ +  SELECT ​[Value] ​AS [Hour] ​ -- list of hours expanded from @crontabExpr 
-    tvf.N AS [Hour] ​ -- translate HOURS field into a list of hours +  FROM [values] ​WHERE FieldName = '​HOURS'​
-  FROM fields f +
-    CROSS APPLY tvf_CrontabFieldValues (FieldValue,​ FieldName) tvf +
-  ​WHERE f.FieldName = '​HOURS'​+
 ) h CROSS JOIN ( ) h CROSS JOIN (
-  SELECT ​ +  SELECT ​[Value] ​AS [DayOfMonth] ​ -- list of days-of-month expanded from @crontabExpr 
-    tvf.N AS [DayOfMonth] ​ -- translate DAYS field into a list of days of the month +  FROM [values] ​WHERE FieldName = '​DAYS'​
-  FROM fields f +
-    CROSS APPLY tvf_CrontabFieldValues (FieldValue,​ FieldName) tvf +
-  ​WHERE f.FieldName = '​DAYS'​+
 ) d CROSS JOIN ( ) d CROSS JOIN (
-  SELECT ​ +  SELECT ​[Value] ​AS [Month] ​ -- list of months ​expanded from @crontabExpr 
-    tvf.N AS [Month] ​ -- translate MONTHS field into a list of months +  FROM [values] ​WHERE FieldName = '​MONTHS'​
-  FROM fields f +
-    CROSS APPLY tvf_CrontabFieldValues (FieldValue,​ FieldName) tvf +
-  ​WHERE f.FieldName = '​MONTHS'​+
 ) mth CROSS JOIN ( ) mth CROSS JOIN (
-  SELECT ​ +  SELECT ​[Value] ​AS [DayOfWeek] ​ -- list of days-of-week expanded from @crontabExpr 
-    tvf.N AS [DayOfWeek] ​ -- translate DAYS-OF-WEEK field into a list of weekdays +  FROM [values] ​WHERE FieldName = '​DAYS-OF-WEEK'​
-  FROM fields f +
-    CROSS APPLY tvf_CrontabFieldValues (FieldValue,​ FieldName) tvf +
-  ​WHERE f.FieldName = '​DAYS-OF-WEEK'​+
 ) w CROSS APPLY (  ) w CROSS APPLY ( 
   -- Field value lists are CROSS JOINed above to generate all matching date patterns.   -- Field value lists are CROSS JOINed above to generate all matching date patterns.
Line 116: Line 108:
  
 ) proto CROSS APPLY ( ) proto CROSS APPLY (
-  -- determine whether the generated ​month has a day of the number from the pattern, and +  -- 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)   -- if so build it (this protects us from invalid DATETIMEFROMPARTS construction)
   SELECT ​   SELECT ​