/****** =============================================
  Author:			Claude AI Code and Jenny McClelland
  Create date:		03/09/2026
				
-- =============================================
-- Next Run Time Calculator for 7-Part Cron Expressions
-- SQL Server 2012+ | Timezone: Central Standard Time
-- Fields: Seconds Minutes Hours DayOfMonth Month DayOfWeek Year

* * * * * * * command
| | | | | | |
| | | | | | +--- Year empty, 1970-2099, not mandatory, allowed (, - * /)
| | | | | +----- Day of the week (1 - 7) (SUN - SAT), allowed (, - * ? / L #)
| | | | +------- Month (1 - 12) or (JAN - DEC), allowed (, - * /)
| | | +--------- Day of the month (1 - 31), allowed (, - * ? / L W)
| | +----------- Hour (0 - 23), allowed (, - * /)
| +------------- Minute (0 - 59), allowed (, - * /)
+--------------- Seconds (0 - 59), allowed (, - * /)
-- =============================================

-- Helper function reference:
--   [dbo].[ufn_org_second_fn_SplitsByIndex](delimiter, string, index)
--	 [dbo].[ufn_org_second_fn_Split](string, delimiter) -- returns [Items]

-- =============================================
-- DAY OF WEEK NAME TO NUMBER MAPPING (SQL Server DATEPART DW)
--   SUN=1, MON=2, TUE=3, WED=4, THU=5, FRI=6, SAT=7
-- =============================================



Summary:
How the stored procedure works across its 6 CTEs:
--https://hexdocs.pm/crontab/cron_notation.html
--https://en.wikipedia.org/wiki/Cron
CronBase — pulls from ServiceJob with your OUTER APPLY and captures the current CST time
CronNormalized — classifies each cron field into a type (ANY, EXACT, STEP, STEP_START, RANGE, LIST, LAST, WEEKDAY, NTH, IGNORE) and normalizes named days/months to numbers
Numbers — generates number of minutes) via recursive CTE; can be adjusted to increase Numbers to 10080 (7 days) or higher if needed
CandidateMinutes — cross joins each job with the numbers to produce required candidate datetimes per job
MatchedCandidates — evaluates 7 cron fields against each distinct candidate datetime
RankedMatches — picks the earliest future match per job

✅ Next run time calculation for all 7 cron fields
✅ All special characters handled (*, /, -, ,, ?, L, W, #)
✅ CST timezone - adjustable to necessary timezone
✅ Returns all jobs, NULL for next run for jobs with no valid firing date (e.g. THU#5 - returns no results in May)
✅ LastSuccessfulRunDateTime and LastStatus from ServiceJob
✅ One row per job

Testing: A few things to be aware of: 
Jobs like THU#5 (5th Thursday) may not fire within 24 hours — increase Numbers to 10080 (7 days) or higher if needed, and adjust MAXRECURSION (if used) accordingly

---- Find all Thursdays in May 2026
SELECT 
    DATEADD(DAY, n, '2026-05-01') AS TheDate,
    DATEPART(DW, DATEADD(DAY, n, '2026-05-01')) AS DW,
    DATENAME(DW, DATEADD(DAY, n, '2026-05-01')) AS DayName,
    (DATEPART(DAY, DATEADD(DAY, n, '2026-05-01')) - 1) / 7 + 1 AS NthOccurrence
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)) AS n(n)
WHERE DATEPART(DW, DATEADD(DAY, n, '2026-05-01')) = 5  -- Thursday

EOMONTH() is used for last-day calculations — available in SQL Server 2012+
The W (nearest weekday) logic handles the edge case where day 1 is a Saturday correctly per the spec

A few things to watch for across the full dataset:

1. THU#5, MON#1 etc. — NTH day jobs may return no result if the nth occurrence doesn't exist in the current or next month. You may want to extend the Numbers CTE lookahead beyond 1440 minutes for those.
2. MON-FRI range — verify the day name normalization is converting correctly to 2-6 for the range comparison.
3. 12,20 hour lists — check TIMEID 107 which has 0 0 12,20 1/1 * ? *.
4. NULL Year — jobs like TIMEID 57, 76, 77 have only 6 parts so CronYear will be NULL, confirm YearType = ANY handles those.
5. Are all jobs returning rows in the final output, including the NULL ones? Can be adjusted to limit to only active jobs
6. JobId 215 — 0 0/15 6-22 * * ? has both a minute step and an hour range — is that returning a correct next run?
7. JobId 119/120/121/122 — 0 0 6,10,12,14,16 ? * MON,TUE * has both a hour list and a DOW list — is that returning the correct earliest next run?

Use Cases:
JobId 20 — 0 30 4 ? * * * (daily 4:30 AM) — should fire daily at 4:30 AM
JobId 107 — 0 0 12,20 1/1 * ? * (hour list) — showing two next run times or just the earliest?
JobId 163 — 0 5 6 ? 5 THU#5 * (5th Thursday of May) — returning a result or no rows?
JobId 173 — 0 0 6-16 1/1 * ? * (hour range) — matching correctly?

Run:
exec  [_org_second_sp_Cron_Next_Run_select] '20, 107, 163, 173, 57, 76, 77, 215, 119, 120, 121, 122,89', NULL --1 Active, 0 Inactive, NULL Both
exec  [_org_second_sp_Cron_Next_Run_select] NULL, 'True'  --'True' Active, 'False' Inactive, NULL Both
exec  [_org_second_sp_Cron_Next_Run_select] '89', 'True'  --'True' Active, 'False' Inactive, NULL Both
exec  [_org_second_sp_Cron_Next_Run_select] '273', 'True', 10080	-- 7 days
exec  [_org_second_sp_Cron_Next_Run_select] NULL, 'True', '86400'	--2 months, 62 days
exec  [_org_second_sp_Cron_Next_Run_select] '210,211,212,213,163,214', 'True', 10080	----Promotion Jobs 

Modifications:
Date:		Author:		Modified:
20260408	JLM			Modified time frame from 7 days (10080) to 31 days (44640) to 2 months (89280);

Security:
--GRANT EXECUTE ON [dbo].[_org_second_sp_Cron_Next_Run_select] TO [RockUser]	
--Modify page security to limit to your Rock Administrator Role or Group Security Role that manages the jobs in Rock
=============================================*******/

ALTER procedure [_org_second_sp_Cron_Next_Run_select] (
	--DECLARE 
	@JobIds varchar(500) = NULL
	, @varIsActive varchar(10) = NULL
	, @varMinutes varchar(6) = NULL
	)
AS

DECLARE @IsActive bit = CASE WHEN @varIsActive = 'True' THEN 1 ELSE 0 END
DECLARE @Minutes int = TRY_CAST(@varMinutes as int)

;WITH 

-- =============================================
-- STEP 1: Get current CST time and split cron fields
-- =============================================
CronBase AS (
    SELECT
        sj.Id  AS JobId,
		sj.[Name] JobName, sj.IsActive,
        sj.CronExpression,
		sj.LastSuccessfulRunDateTime, 
		sj.LastStatus,
        -- Current CST datetime
        CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME) AS NowCST,

        -- Raw cron parts from OUTER APPLY
        CronParts.CronSecond,
        CronParts.CronMinute,
        CronParts.CronHour,
        CronParts.CronDayOfMonth,
        CronParts.CronMonth,
        CronParts.CronDayOfWeek,
        CronParts.CronYear

    FROM ServiceJob sj
    OUTER APPLY (
        SELECT
            LTRIM(RTRIM([dbo].[ufn_org_second_fn_SplitsByIndex](' ', sj.CronExpression, 1))) AS CronSecond,
            LTRIM(RTRIM([dbo].[ufn_org_second_fn_SplitsByIndex](' ', sj.CronExpression, 2))) AS CronMinute,
            LTRIM(RTRIM([dbo].[ufn_org_second_fn_SplitsByIndex](' ', sj.CronExpression, 3))) AS CronHour,
            LTRIM(RTRIM([dbo].[ufn_org_second_fn_SplitsByIndex](' ', sj.CronExpression, 4))) AS CronDayOfMonth,
            LTRIM(RTRIM([dbo].[ufn_org_second_fn_SplitsByIndex](' ', sj.CronExpression, 5))) AS CronMonth,
            LTRIM(RTRIM([dbo].[ufn_org_second_fn_SplitsByIndex](' ', sj.CronExpression, 6))) AS CronDayOfWeek,
            LTRIM(RTRIM([dbo].[ufn_org_second_fn_SplitsByIndex](' ', sj.CronExpression, 7))) AS CronYear
    ) AS CronParts
    WHERE (sj.IsActive = @IsActive OR @IsActive IS NULL) AND  -- Uncomment to filter active jobs only
		 (sj.Id IN (SELECT [Items] FROM [dbo].[ufn_org_second_fn_Split] (@JobIds, ',')) or @JobIds IS NULL)
),

-- =============================================
-- STEP 2: Resolve named days/months to numbers
--         and pre-parse special characters
-- =============================================
CronNormalized AS (
    SELECT
        JobId,
		JobName, IsActive,
        CronExpression,
		LastSuccessfulRunDateTime, 
		LastStatus,
        NowCST,
        CronSecond,
        CronMinute,
        CronHour,
        CronDayOfMonth,
        CronMonth,
        CronDayOfWeek,
        CronYear,

        -- -----------------------------------------------
        -- SECOND: numeric only in your data (0 or 30)
        -- -----------------------------------------------
        CASE
            WHEN CronSecond = '*'                           THEN 'ANY'
            WHEN CronSecond LIKE '*/%'                      THEN 'STEP'
            WHEN CronSecond LIKE '[0-9]%/[0-9]%'            THEN 'STEP_START'   -- e.g. 1/10
            WHEN CronSecond LIKE '%,%'                      THEN 'LIST'
            WHEN CronSecond LIKE '%-%'                      THEN 'RANGE'
            WHEN CronSecond NOT LIKE '%[^0-9]%'             THEN 'EXACT'
            ELSE 'ANY'
        END AS SecondType,
		--CASE
  --          WHEN CronSecond = '*'                           THEN -1     -- every second
  --          WHEN CronSecond LIKE '*/%'                      THEN CAST(SUBSTRING(CronSecond, 3, LEN(CronSecond)) AS INT)  -- step
  --          WHEN CronSecond NOT LIKE '%[^0-9]%'             THEN CAST(CronSecond AS INT)  -- exact
  --          ELSE 0
  --      END AS SecondValue,
		CASE
            WHEN CronSecond = '*'                           THEN -1     -- every second
            WHEN CronSecond LIKE '*/%'                      THEN CAST(SUBSTRING(CronSecond, 3, LEN(CronSecond)) AS INT)  --every minute
            WHEN CronSecond LIKE '[0-9]%/[0-9]%'            THEN CAST(SUBSTRING(CronSecond, CHARINDEX('/', CronSecond) + 1, LEN(CronSecond)) AS INT) --step
            WHEN CronSecond NOT LIKE '%[^0-9]%'				THEN CAST(CronSecond AS INT)  -- exact
            WHEN CronSecond LIKE '%-%' AND CronSecond NOT LIKE '%,%'
                THEN CAST(LEFT(CronSecond, CHARINDEX('-', CronSecond) - 1) AS INT) --range
            ELSE 0
        END AS SecondValue1,  -- step interval, exact value, or range start
        CASE
            WHEN CronSecond LIKE '*/%'                      THEN 0   -- starts at 0
            WHEN CronSecond LIKE '[0-9]%/[0-9]%'           THEN CAST(LEFT(CronSecond, CHARINDEX('/', CronSecond) - 1) AS INT)
            WHEN CronSecond LIKE '%-%' AND CronSecond NOT LIKE '%,%'
                THEN CAST(SUBSTRING(CronSecond, CHARINDEX('-', CronSecond) + 1, LEN(CronSecond)) AS INT)
            ELSE 0
        END AS SecondValue2,  -- range end or step start offset
        
        -- -----------------------------------------------
        -- MINUTE
        -- -----------------------------------------------
        CASE
            WHEN CronMinute = '*'                           THEN 'ANY'
            WHEN CronMinute LIKE '*/%'                      THEN 'STEP'
            WHEN CronMinute LIKE '[0-9]%/[0-9]%'            THEN 'STEP_START'   -- e.g. 1/10
            WHEN CronMinute LIKE '%,%'                      THEN 'LIST'
            WHEN CronMinute LIKE '%-%'                      THEN 'RANGE'
            WHEN CronMinute NOT LIKE '%[^0-9]%'             THEN 'EXACT'
            ELSE 'ANY'
        END AS MinuteType,
        CASE
            WHEN CronMinute LIKE '*/%'                      THEN CAST(SUBSTRING(CronMinute, 3, LEN(CronMinute)) AS INT)  --every minute
            WHEN CronMinute LIKE '[0-9]%/[0-9]%'            THEN CAST(SUBSTRING(CronMinute, CHARINDEX('/', CronMinute) + 1, LEN(CronMinute)) AS INT) --step
            WHEN CronMinute NOT LIKE '%[^0-9]%'				THEN CAST(CronMinute AS INT)  -- exact
            WHEN CronMinute LIKE '%-%' AND CronMinute NOT LIKE '%,%'
                THEN CAST(LEFT(CronMinute, CHARINDEX('-', CronMinute) - 1) AS INT) --range
            ELSE 0
        END AS MinuteValue1,  -- step interval, exact value, or range start
        CASE
            WHEN CronMinute LIKE '*/%'                      THEN 0   -- starts at 0
            WHEN CronMinute LIKE '[0-9]%/[0-9]%'           THEN CAST(LEFT(CronMinute, CHARINDEX('/', CronMinute) - 1) AS INT)
            WHEN CronMinute LIKE '%-%' AND CronMinute NOT LIKE '%,%'
                THEN CAST(SUBSTRING(CronMinute, CHARINDEX('-', CronMinute) + 1, LEN(CronMinute)) AS INT)
            ELSE 0
        END AS MinuteValue2,  -- range end or step start offset

        -- -----------------------------------------------
        -- HOUR
        -- -----------------------------------------------
        CASE
            WHEN CronHour = '*'                             THEN 'ANY'
            WHEN CronHour LIKE '*/%'                        THEN 'STEP'
            WHEN CronHour LIKE '[0-9]%/[0-9]%'             THEN 'STEP_START'
            WHEN CronHour LIKE '%,%'                        THEN 'LIST'
            WHEN CronHour LIKE '%-%'                        THEN 'RANGE'
            WHEN CronHour NOT LIKE '%[^0-9]%'               THEN 'EXACT'
            ELSE 'ANY'
        END AS HourType,
        CASE
            WHEN CronHour LIKE '*/%'                        THEN CAST(SUBSTRING(CronHour, 3, LEN(CronHour)) AS INT)
            WHEN CronHour LIKE '[0-9]%/[0-9]%'             THEN CAST(SUBSTRING(CronHour, CHARINDEX('/', CronHour) + 1, LEN(CronHour)) AS INT)
            WHEN CronHour NOT LIKE '%[^0-9]%'               THEN CAST(CronHour AS INT)
            WHEN CronHour LIKE '%-%' AND CronHour NOT LIKE '%,%'
                THEN CAST(LEFT(CronHour, CHARINDEX('-', CronHour) - 1) AS INT)
            ELSE 0
        END AS HourValue1,
        CASE
            WHEN CronHour LIKE '*/%'                        THEN 0
            WHEN CronHour LIKE '[0-9]%/[0-9]%'             THEN CAST(LEFT(CronHour, CHARINDEX('/', CronHour) - 1) AS INT)
            WHEN CronHour LIKE '%-%' AND CronHour NOT LIKE '%,%'
                THEN CAST(SUBSTRING(CronHour, CHARINDEX('-', CronHour) + 1, LEN(CronHour)) AS INT)
            ELSE 0
        END AS HourValue2,

        -- -----------------------------------------------
        -- DAY OF MONTH
        -- -----------------------------------------------
        CASE
            WHEN CronDayOfMonth = '*'                       THEN 'ANY'
            WHEN CronDayOfMonth = '?'                       THEN 'IGNORE'
            WHEN CronDayOfMonth = 'L'                       THEN 'LAST'
            WHEN CronDayOfMonth LIKE '%W'                   THEN 'WEEKDAY'     -- e.g. 15W
            WHEN CronDayOfMonth LIKE '[0-9]%/[0-9]%'       THEN 'STEP_START'  -- e.g. 1/1, 1/5
            WHEN CronDayOfMonth LIKE '%,%'                  THEN 'LIST'
            WHEN CronDayOfMonth LIKE '%-%'                  THEN 'RANGE'
            WHEN CronDayOfMonth NOT LIKE '%[^0-9]%'         THEN 'EXACT'
            ELSE 'ANY'
        END AS DayOfMonthType,
        CASE
            WHEN CronDayOfMonth LIKE '[0-9]%/[0-9]%'       THEN CAST(LEFT(CronDayOfMonth, CHARINDEX('/', CronDayOfMonth) - 1) AS INT)
            WHEN CronDayOfMonth LIKE '%W'                   THEN CAST(LEFT(CronDayOfMonth, LEN(CronDayOfMonth) - 1) AS INT)
            WHEN CronDayOfMonth NOT LIKE '%[^0-9]%'         THEN CAST(CronDayOfMonth AS INT)
            ELSE 1
        END AS DayOfMonthValue1,  -- start/exact/weekday target day
        CASE
            WHEN CronDayOfMonth LIKE '[0-9]%/[0-9]%'       THEN CAST(SUBSTRING(CronDayOfMonth, CHARINDEX('/', CronDayOfMonth) + 1, LEN(CronDayOfMonth)) AS INT)
            ELSE 1
        END AS DayOfMonthValue2,  -- step interval

        -- -----------------------------------------------
        -- MONTH
        -- Normalize named months to numbers
        -- -----------------------------------------------
        CASE
            WHEN CronMonth = '*'                            THEN 'ANY'
            WHEN CronMonth LIKE '[0-9]%/[0-9]%'            THEN 'STEP_START'
            WHEN CronMonth LIKE '%,%'                       THEN 'LIST'
            WHEN CronMonth LIKE '%-%'                       THEN 'RANGE'
            WHEN CronMonth NOT LIKE '%[^0-9]%'              THEN 'EXACT'
            ELSE 'ANY'
        END AS MonthType,
        -- Normalize month string: replace names with numbers
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            UPPER(CronMonth),
        'JAN','1'),'FEB','2'),'MAR','3'),'APR','4'),
        'MAY','5'),'JUN','6'),'JUL','7'),'AUG','8'),
        'SEP','9'),'OCT','10'),'NOV','11'),'DEC','12')
            AS MonthNormalized,

        -- -----------------------------------------------
        -- DAY OF WEEK
        -- Normalize named days to SQL Server DW numbers
        -- SUN=1, MON=2, TUE=3, WED=4, THU=5, FRI=6, SAT=7
        -- -----------------------------------------------
        CASE
            WHEN CronDayOfWeek = '*'                        THEN 'ANY'
            WHEN CronDayOfWeek = '?'                        THEN 'IGNORE'
            WHEN CronDayOfWeek LIKE '%L'                    THEN 'LAST_DOW'    -- e.g. 6L = last Friday
            WHEN CronDayOfWeek LIKE '%#%'                   THEN 'NTH'         -- e.g. MON#1, THU#5
            WHEN CronDayOfWeek LIKE '%,%'                   THEN 'LIST'
            WHEN CronDayOfWeek LIKE '%-%'                   THEN 'RANGE'
            WHEN CronDayOfWeek NOT LIKE '%[^0-9]%'          THEN 'EXACT'
            ELSE 'NAMED'
        END AS DayOfWeekType,
        -- Normalize DOW string: replace names with SQL Server DW numbers
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            UPPER(CronDayOfWeek),
        'SUN','1'),'MON','2'),'TUE','3'),'WED','4'),
        'THU','5'),'FRI','6'),'SAT','7')
            AS DayOfWeekNormalized,

        -- -----------------------------------------------
        -- YEAR
        -- -----------------------------------------------
        CASE
            WHEN CronYear IS NULL OR CronYear = '*'         THEN 'ANY'
            WHEN CronYear LIKE '%-%'                        THEN 'RANGE'
            WHEN CronYear NOT LIKE '%[^0-9]%'               THEN 'EXACT'
            ELSE 'ANY'
        END AS YearType,
        CASE
            WHEN CronYear NOT LIKE '%[^0-9]%' AND CronYear IS NOT NULL
                THEN CAST(CronYear AS INT)
            WHEN CronYear LIKE '%-%'
                THEN CAST(LEFT(CronYear, CHARINDEX('-', CronYear) - 1) AS INT)
            ELSE NULL
        END AS YearValue1,
        CASE
            WHEN CronYear LIKE '%-%'
                THEN CAST(SUBSTRING(CronYear, CHARINDEX('-', CronYear) + 1, LEN(CronYear)) AS INT)
            ELSE NULL
        END AS YearValue2

    FROM CronBase
),
--)
--SELECT JobId, CronMinute, MinuteType, MinuteValue1, MinuteValue2
--FROM CronNormalized
--WHERE JobId in (163,210,211,212,213,214)

-- =============================================
-- STEP 3: Generate candidate next-run datetimes
--         by iterating forward up to 366 days
--         Uses a numbers table approach via recursive CTE
--		Look ahead up to 1440 minutes (24 hours)
--		10080 (7 days) or 44640 (31 days) or 86400 (60 days) 
--		Optionally add 129,600 (90 days) or 259,200 (6 mths - 180 days) or 527,040 (366 days)

-- NUMBERS CTE REquirement:
--   The Numbers CTE uses sys.objects cross joined with itself to generate
--   the required number of rows for the lookahead window. This requires
--   sys.objects to have at least SQRT(@Minutes) rows.
--
--   Minimum sys.objects rows required per lookahead window:
--     1,440   (1 day)    = 38 rows
--     10,080  (7 days)   = 101 rows
--     44,640  (31 days)  = 212 rows
--     129,600 (90 days)  = 360 rows
--     259,200 (180 days) = 509 rows
--     527,040 (366 days) = 726 rows
--
--   To verify your sys.objects row count:
--     SELECT COUNT(*) FROM sys.objects
--
--   If sys.objects does not have enough rows, add a third cross join:
--     FROM sys.objects a
--     CROSS JOIN sys.objects b
--     CROSS JOIN sys.objects c
-- =============================================
Numbers AS (  
    SELECT TOP	(@Minutes)	-- 62 days (approx 2 months ahead)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
    FROM sys.objects a
    CROSS JOIN sys.objects b
),

-- Build candidate minutes from now
CandidateMinutes AS (
    SELECT
        cn.JobId,
		cn.JobName, cn.IsActive,
        cn.CronExpression,
		cn.LastSuccessfulRunDateTime, 
		cn.LastStatus,
        cn.NowCST,
        cn.CronSecond,   cn.SecondType,		cn.SecondValue1,  cn.SecondValue2, 
        cn.CronMinute,   cn.MinuteType,   cn.MinuteValue1, cn.MinuteValue2,
        cn.CronHour,     cn.HourType,     cn.HourValue1,   cn.HourValue2,
        cn.CronDayOfMonth, cn.DayOfMonthType, cn.DayOfMonthValue1, cn.DayOfMonthValue2,
        cn.CronMonth,    cn.MonthType,    cn.MonthNormalized,
        cn.CronDayOfWeek, cn.DayOfWeekType, cn.DayOfWeekNormalized,
        cn.CronYear,     cn.YearType,     cn.YearValue1,   cn.YearValue2,

        ---- Candidate datetime: now + N minutes (rounded to second=0)
        --DATEADD(SECOND,
        --    -- snap to the correct second
        --    CASE
        --        WHEN cn.SecondType = 'EXACT' THEN cn.SecondValue
        --        WHEN cn.SecondType = 'STEP'  THEN 0
        --        ELSE 0
        --    END
        --    - DATEPART(SECOND, cn.NowCST),
        --    DATEADD(MINUTE, n.N,
        --        -- strip seconds from now
        --        DATEADD(SECOND, -DATEPART(SECOND, cn.NowCST), cn.NowCST)
        --    )
        --) AS CandidateDT

		-- In CandidateMinutes, replace the CandidateDT calculation with:
		DATEADD(SECOND,
			CASE
				WHEN cn.SecondType = 'EXACT' THEN cn.SecondValue1  -- add back exact second
				ELSE 0
			END,
			DATEADD(MINUTE, n.N,
				DATEADD(SECOND, -DATEPART(SECOND, cn.NowCST),
					DATEADD(MILLISECOND, -DATEPART(MILLISECOND, cn.NowCST), cn.NowCST)
				)
			)
		) AS CandidateDT

		---- Strip both seconds AND milliseconds, then add N minutes
		--	DATEADD(MINUTE, n.N,
		--		DATEADD(SECOND, -DATEPART(SECOND, cn.NowCST),
		--			DATEADD(MILLISECOND, -DATEPART(MILLISECOND, cn.NowCST), cn.NowCST)
		--		)
		--	) AS CandidateDT

        , n.N AS MinuteOffset

    FROM CronNormalized cn
    CROSS JOIN Numbers n
),
--)
--SELECT *
--FROM CandidateMinutes
--WHERE JobId in (163)

-- =============================================
-- STEP 4: Filter candidates that match all cron fields
-- =============================================
MatchedCandidates AS (
    SELECT distinct
        JobId,
		JobName, IsActive,
        CronExpression,
		LastSuccessfulRunDateTime, 
		LastStatus,
        NowCST,
        CandidateDT,
        MinuteOffset,

        -- Extract date parts from candidate
        DATEPART(SECOND,      CandidateDT) AS cSec,
        DATEPART(MINUTE,      CandidateDT) AS cMin,
        DATEPART(HOUR,        CandidateDT) AS cHour,
        DATEPART(DAY,         CandidateDT) AS cDay,
        DATEPART(MONTH,       CandidateDT) AS cMonth,
        DATEPART(YEAR,        CandidateDT) AS cYear,
        DATEPART(DW,          CandidateDT) AS cDOW,   -- 1=Sun..7=Sat
        DAY(EOMONTH(CandidateDT))          AS cLastDayOfMonth,

        -- -----------------------------------------------
        -- SECOND MATCH
        -- -----------------------------------------------
        --CASE
        --    WHEN SecondType = 'ANY'     THEN 1
        --    WHEN SecondType = 'EXACT'   AND DATEPART(SECOND, CandidateDT) = SecondValue    THEN 1
        --    WHEN SecondType = 'STEP'    AND DATEPART(SECOND, CandidateDT) % SecondValue = 0 THEN 1
        --    ELSE 0
        --END AS SecondMatch,

		CASE
            WHEN SecondType = 'ANY'         THEN 1
            WHEN SecondType = 'EXACT'       AND DATEPART(SECOND, CandidateDT) = SecondValue1   THEN 1
            WHEN SecondType = 'STEP'        AND DATEPART(SECOND, CandidateDT) % SecondValue1 = 0 THEN 1
            WHEN SecondType = 'STEP_START'  AND DATEPART(SECOND, CandidateDT) >= SecondValue2
                                            AND (DATEPART(SECOND, CandidateDT) - SecondValue2) % SecondValue1 = 0 THEN 1
            WHEN SecondType = 'RANGE'       AND DATEPART(SECOND, CandidateDT) BETWEEN SecondValue1 AND SecondValue2 THEN 1
            WHEN SecondType = 'LIST'        AND ',' + CronSecond + ',' LIKE '%,' + CAST(DATEPART(SECOND, CandidateDT) AS VARCHAR) + ',%' THEN 1
            ELSE 0
        END AS SecondMatch,
        -- -----------------------------------------------
        -- MINUTE MATCH
        -- -----------------------------------------------
        CASE
            WHEN MinuteType = 'ANY'         THEN 1
            WHEN MinuteType = 'EXACT'       AND DATEPART(MINUTE, CandidateDT) = MinuteValue1   THEN 1
            WHEN MinuteType = 'STEP'        AND DATEPART(MINUTE, CandidateDT) % MinuteValue1 = 0 THEN 1
            WHEN MinuteType = 'STEP_START'  AND DATEPART(MINUTE, CandidateDT) >= MinuteValue2
                                            AND (DATEPART(MINUTE, CandidateDT) - MinuteValue2) % MinuteValue1 = 0 THEN 1
            WHEN MinuteType = 'RANGE'       AND DATEPART(MINUTE, CandidateDT) BETWEEN MinuteValue1 AND MinuteValue2 THEN 1
            WHEN MinuteType = 'LIST'        AND ',' + CronMinute + ',' LIKE '%,' + CAST(DATEPART(MINUTE, CandidateDT) AS VARCHAR) + ',%' THEN 1
            ELSE 0
        END AS MinuteMatch,

        -- -----------------------------------------------
        -- HOUR MATCH
        -- -----------------------------------------------
        CASE
            WHEN HourType = 'ANY'           THEN 1
            WHEN HourType = 'EXACT'         AND DATEPART(HOUR, CandidateDT) = HourValue1       THEN 1
            WHEN HourType = 'STEP'          AND DATEPART(HOUR, CandidateDT) % HourValue1 = 0   THEN 1
            WHEN HourType = 'STEP_START'    AND DATEPART(HOUR, CandidateDT) >= HourValue2
                                            AND (DATEPART(HOUR, CandidateDT) - HourValue2) % HourValue1 = 0 THEN 1
            WHEN HourType = 'RANGE'         AND DATEPART(HOUR, CandidateDT) BETWEEN HourValue1 AND HourValue2 THEN 1
            WHEN HourType = 'LIST'          AND ',' + CronHour + ',' LIKE '%,' + CAST(DATEPART(HOUR, CandidateDT) AS VARCHAR) + ',%' THEN 1
            ELSE 0
        END AS HourMatch,

        -- -----------------------------------------------
        -- DAY OF MONTH MATCH
        -- -----------------------------------------------
        CASE
            WHEN DayOfMonthType = 'ANY'         THEN 1
            WHEN DayOfMonthType = 'IGNORE'      THEN 1  -- deferred to DOW
            WHEN DayOfMonthType = 'EXACT'       AND DATEPART(DAY, CandidateDT) = DayOfMonthValue1  THEN 1
            WHEN DayOfMonthType = 'LAST'        AND DATEPART(DAY, CandidateDT) = DAY(EOMONTH(CandidateDT)) THEN 1
            WHEN DayOfMonthType = 'STEP_START'  AND DATEPART(DAY, CandidateDT) >= DayOfMonthValue1
                                                AND (DATEPART(DAY, CandidateDT) - DayOfMonthValue1) % DayOfMonthValue2 = 0 THEN 1
            WHEN DayOfMonthType = 'RANGE'       AND DATEPART(DAY, CandidateDT)
                                                    BETWEEN CAST(LEFT(CronDayOfMonth, CHARINDEX('-', CronDayOfMonth) - 1) AS INT)
                                                    AND     CAST(SUBSTRING(CronDayOfMonth, CHARINDEX('-', CronDayOfMonth) + 1, LEN(CronDayOfMonth)) AS INT) THEN 1
            WHEN DayOfMonthType = 'LIST'        AND ',' + CronDayOfMonth + ',' LIKE '%,' + CAST(DATEPART(DAY, CandidateDT) AS VARCHAR) + ',%' THEN 1
            -- W = nearest weekday to target day
            WHEN DayOfMonthType = 'WEEKDAY'     AND (
                -- target day is a weekday: exact match
                (DATEPART(DW, CandidateDT) BETWEEN 2 AND 6 AND DATEPART(DAY, CandidateDT) = DayOfMonthValue1)
                -- target is Saturday: fire Friday before (unless day 1, then Monday after)
                OR (DATEPART(DW, DATEADD(DAY, DayOfMonthValue1 - DATEPART(DAY, CandidateDT), CandidateDT)) = 7
                    AND DATEPART(DW, CandidateDT) = 6
                    AND DATEPART(DAY, CandidateDT) = DayOfMonthValue1 - 1
                    AND DayOfMonthValue1 > 1)
                OR (DATEPART(DW, DATEADD(DAY, DayOfMonthValue1 - DATEPART(DAY, CandidateDT), CandidateDT)) = 7
                    AND DATEPART(DW, CandidateDT) = 2
                    AND DATEPART(DAY, CandidateDT) = DayOfMonthValue1 + 2
                    AND DayOfMonthValue1 = 1)
                -- target is Sunday: fire Monday after
                OR (DATEPART(DW, DATEADD(DAY, DayOfMonthValue1 - DATEPART(DAY, CandidateDT), CandidateDT)) = 1
                    AND DATEPART(DW, CandidateDT) = 2
                    AND DATEPART(DAY, CandidateDT) = DayOfMonthValue1 + 1)
            ) THEN 1
            ELSE 0
        END AS DayOfMonthMatch,

        -- -----------------------------------------------
        -- MONTH MATCH
        -- -----------------------------------------------
        CASE
            WHEN MonthType = 'ANY'          THEN 1
            WHEN MonthType = 'EXACT'        AND DATEPART(MONTH, CandidateDT) = CAST(MonthNormalized AS INT) THEN 1
            WHEN MonthType = 'STEP_START'   AND DATEPART(MONTH, CandidateDT) >= CAST(LEFT(MonthNormalized, CHARINDEX('/', MonthNormalized) - 1) AS INT)
                                            AND (DATEPART(MONTH, CandidateDT) - CAST(LEFT(MonthNormalized, CHARINDEX('/', MonthNormalized) - 1) AS INT))
                                                % CAST(SUBSTRING(MonthNormalized, CHARINDEX('/', MonthNormalized) + 1, LEN(MonthNormalized)) AS INT) = 0 THEN 1
            WHEN MonthType = 'RANGE'        AND DATEPART(MONTH, CandidateDT)
                                                BETWEEN CAST(LEFT(MonthNormalized, CHARINDEX('-', MonthNormalized) - 1) AS INT)
                                                AND     CAST(SUBSTRING(MonthNormalized, CHARINDEX('-', MonthNormalized) + 1, LEN(MonthNormalized)) AS INT) THEN 1
            WHEN MonthType = 'LIST'         AND ',' + MonthNormalized + ',' LIKE '%,' + CAST(DATEPART(MONTH, CandidateDT) AS VARCHAR) + ',%' THEN 1
            ELSE 0
        END AS MonthMatch,

        -- -----------------------------------------------
        -- DAY OF WEEK MATCH
        -- SUN=1, MON=2, TUE=3, WED=4, THU=5, FRI=6, SAT=7
        -- -----------------------------------------------
        CASE
            WHEN DayOfWeekType = 'ANY'      THEN 1
            WHEN DayOfWeekType = 'IGNORE'   THEN 1  -- deferred to DayOfMonth
            WHEN DayOfWeekType = 'EXACT'    AND DATEPART(DW, CandidateDT) = CAST(DayOfWeekNormalized AS INT) THEN 1
            WHEN DayOfWeekType = 'NAMED'    AND DATEPART(DW, CandidateDT) = CAST(DayOfWeekNormalized AS INT) THEN 1
            WHEN DayOfWeekType = 'RANGE'    AND DATEPART(DW, CandidateDT)
                                                BETWEEN CAST(LEFT(DayOfWeekNormalized, CHARINDEX('-', DayOfWeekNormalized) - 1) AS INT)
                                                AND     CAST(SUBSTRING(DayOfWeekNormalized, CHARINDEX('-', DayOfWeekNormalized) + 1, LEN(DayOfWeekNormalized)) AS INT) THEN 1
            WHEN DayOfWeekType = 'LIST'     AND ',' + DayOfWeekNormalized + ',' LIKE '%,' + CAST(DATEPART(DW, CandidateDT) AS VARCHAR) + ',%' THEN 1
            -- LAST: e.g. 6L = last Friday of month
            WHEN DayOfWeekType = 'LAST_DOW' AND
                DATEPART(DW, CandidateDT) = CAST(LEFT(DayOfWeekNormalized, CHARINDEX('L', DayOfWeekNormalized) - 1) AS INT)
                AND DATEPART(DAY, DATEADD(DAY, 7, CandidateDT)) > DAY(EOMONTH(CandidateDT)) THEN 1
            -- NTH: e.g. MON#1 = 1st Monday, THU#5 = 5th Thursday
            WHEN DayOfWeekType = 'NTH'      AND
                DATEPART(DW, CandidateDT) = CAST(LEFT(DayOfWeekNormalized, CHARINDEX('#', DayOfWeekNormalized) - 1) AS INT)
                AND (DATEPART(DAY, CandidateDT) - 1) / 7 + 1 =
                    CAST(SUBSTRING(DayOfWeekNormalized, CHARINDEX('#', DayOfWeekNormalized) + 1, LEN(DayOfWeekNormalized)) AS INT) THEN 1
            ELSE 0
        END AS DayOfWeekMatch,

        -- -----------------------------------------------
        -- YEAR MATCH
        -- -----------------------------------------------
        CASE
            WHEN YearType = 'ANY'           THEN 1
            WHEN YearType = 'EXACT'         AND DATEPART(YEAR, CandidateDT) = YearValue1 THEN 1
            WHEN YearType = 'RANGE'         AND DATEPART(YEAR, CandidateDT) BETWEEN YearValue1 AND YearValue2 THEN 1
            ELSE 0
        END AS YearMatch,

        -- Exclude candidates in the past
        CASE WHEN CandidateDT > NowCST THEN 1 ELSE 0 END AS IsFuture,

        -- Pass through for final select
        CronSecond, CronMinute, CronHour, CronDayOfMonth,
        CronMonth, CronDayOfWeek, CronYear,
        SecondType, MinuteType, HourType, DayOfMonthType,
        MonthType, DayOfWeekType, YearType,
        MinuteValue1, MinuteValue2, HourValue1, HourValue2,
        DayOfMonthValue1, DayOfMonthValue2,
        MonthNormalized, DayOfWeekNormalized,
        YearValue1, YearValue2, SecondValue1, SecondValue2

    FROM CandidateMinutes
),
--)
--SELECT --TOP 20
--    JobId,
--    CandidateDT,
--    DATEPART(SECOND, CandidateDT) AS cSec,
--    DATEPART(MINUTE, CandidateDT) AS cMin,
--    DATEPART(HOUR,   CandidateDT) AS cHour,
--    SecondMatch,
--    MinuteMatch,
--    HourMatch,
--    DayOfMonthMatch,
--    MonthMatch,
--    DayOfWeekMatch,
--    YearMatch,
--    IsFuture
--FROM MatchedCandidates
--WHERE JobId = 163
--AND DATEPART(HOUR, CandidateDT) = 6  -- show only 4 AM candidates
--AND DATEPART(MONTH, CandidateDT) = 5  --only show May candidates
--AND DATEPART(DAY, CandidateDT) = 29  --only show day 29
--ORDER BY CandidateDT

--SELECT 
--    JobId,
--    CronMinute,
--    LEN(CronMinute)         AS Len,
--    CAST(CronMinute AS INT) AS CastToInt,
--    MinuteType,
--    MinuteValue1,
--    DATEPART(MINUTE, CandidateDT) AS cMin,
--    CASE 
--        WHEN DATEPART(MINUTE, CandidateDT) = MinuteValue1 THEN 'MATCH'
--        ELSE 'NO MATCH'
--    END AS DirectCompare
--FROM MatchedCandidates
--WHERE JobId = 20
--AND DATEPART(MINUTE, CandidateDT) = 30

--SELECT distinct
--    JobId,
--    CronMinute,
--    MinuteType,
--    MinuteValue1,
--    DATEPART(MINUTE, CandidateDT) AS cMin,
--    DATEPART(MINUTE, CandidateDT) - MinuteValue1 AS Difference,
--    SQL_VARIANT_PROPERTY(MinuteValue1, 'BaseType') AS MinuteValue1Type
--FROM MatchedCandidates
--WHERE JobId = 20
--AND DATEPART(MINUTE, CandidateDT) = 30

--SELECT distinct 
--    JobId,
--    CronMinute,
--    MinuteType,
--    MinuteValue1,
--    DATEPART(MINUTE, CandidateDT) AS cMin,
--    CASE
--        WHEN MinuteType = 'ANY'         THEN 1
--        WHEN MinuteType = 'EXACT'       AND DATEPART(MINUTE, CandidateDT) = MinuteValue1   THEN 1
--        WHEN MinuteType = 'STEP'        AND DATEPART(MINUTE, CandidateDT) % MinuteValue1 = 0 THEN 1
--        WHEN MinuteType = 'STEP_START'  AND DATEPART(MINUTE, CandidateDT) >= MinuteValue2
--                                        AND (DATEPART(MINUTE, CandidateDT) - MinuteValue2) % MinuteValue1 = 0 THEN 1
--        WHEN MinuteType = 'RANGE'       AND DATEPART(MINUTE, CandidateDT) BETWEEN MinuteValue1 AND MinuteValue2 THEN 1
--        WHEN MinuteType = 'LIST'        AND ',' + CronMinute + ',' LIKE '%,' + CAST(DATEPART(MINUTE, CandidateDT) AS VARCHAR) + ',%' THEN 1
--        ELSE 0
--    END AS MinuteMatchTest
--FROM MatchedCandidates
--WHERE JobId = 20
--AND DATEPART(MINUTE, CandidateDT) = 30


-- =============================================
-- STEP 5: Keep only candidates where ALL fields match
--         and pick the earliest future one per job
-- =============================================
FilteredMatches AS (
    SELECT distinct *
    FROM MatchedCandidates
    WHERE
        SecondMatch			= 1
        AND MinuteMatch     = 1
        AND HourMatch       = 1
        AND DayOfMonthMatch = 1
        AND MonthMatch      = 1
        AND DayOfWeekMatch  = 1
        AND YearMatch       = 1
        AND IsFuture        = 1
),

RankedMatches AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY JobId ORDER BY CandidateDT ASC) AS RN
    FROM FilteredMatches
),

-- All jobs, with NULL if no match found
AllJobs AS (
    SELECT DISTINCT
        JobId,
		JobName, IsActive,
        CronExpression,
		LastSuccessfulRunDateTime, 
		LastStatus,
        NowCST,
        CronSecond, CronMinute, CronHour, CronDayOfMonth,
        CronMonth, CronDayOfWeek, CronYear,
        SecondType, MinuteType, HourType, DayOfMonthType,
        MonthType, DayOfWeekType, YearType
    FROM CronNormalized
)

-- =============================================
-- STEP 6: Final output
-- =============================================


SELECT
    aj.JobId,
	aj.JobName, aj.IsActive,
    aj.CronExpression,
	aj.LastSuccessfulRunDateTime, 
	aj.LastStatus,
    aj.NowCST                                           AS CurrentTimeCST,
    rm.CandidateDT                                      AS NextRunCST,  -- NULL if no match
   -- CONVERT(VARCHAR, rm.CandidateDT, 120)               AS NextRunFormatted,
    aj.CronSecond      + ' (' + aj.SecondType    + ')'  AS SecondField,
    aj.CronMinute      + ' (' + aj.MinuteType    + ')'  AS MinuteField,
    aj.CronHour        + ' (' + aj.HourType      + ')'  AS HourField,
    aj.CronDayOfMonth  + ' (' + aj.DayOfMonthType + ')' AS DayOfMonthField,
    aj.CronMonth       + ' (' + aj.MonthType     + ')'  AS MonthField,
    aj.CronDayOfWeek   + ' (' + aj.DayOfWeekType + ')'  AS DayOfWeekField,
    ISNULL(aj.CronYear, 'NULL') + ' (' + aj.YearType + ')' AS YearField
FROM AllJobs aj
LEFT JOIN RankedMatches rm
    ON rm.JobId = aj.JobId
    AND rm.RN = 1
ORDER BY aj.IsActive, rm.CandidateDT

--OPTION (MAXRECURSION 1500);  -- allow recursion up to 1500 for the Numbers CTE
--SELECT [Id] [Value], [Name] + ' (' + CASE WHEN [IsActive] = 1 THEN 'Active' ELSE 'Inactive' END + ')' AS [TEXT]
--FROM [dbo].[ServiceJob]
--ORDER BY [Name]