Using sysschedules like table for events SQL Server

Question

I am trying to develop a recurring event calendar. Looks like the sysschedules table in the msdb database might do the job just fine.

However I am facing few challenges, hoping somebody could help.

a) active_start_date: is this the date first time an event supposed to occur? Or any date on or before the event start?

b) how can I find the matching events (once, daily, weekly, monthly etc) for a specific date?

Edit: I am not directly using the sysschedules table. Rather created a new table with same columns.

Answer 1

First, you should refer sysschedules. But as far as I understand there is a typo. This

active_start_time Time on any day between active_start_date and active_end_date that job begins executing. Time is formatted HHMMSS, using a 24-hour clock.

should be read as

active_start_time Time on any day between active_start_time and active_end_time that job begins executing. Time is formatted HHMMSS, using a 24-hour clock.

And consider this:

active_start_date Date on which execution of a job can begin. The date is formatted as YYYYMMDD. NULL indicates today's date.

That means if you configure recurring job to start every day every hour and set active_start_time = 100000 and active_start_date = 20120323 then job won't run till 23 March 2012 10 AM.
But if you're creating once-running task then this fields are containing exact start date and time.

As for finding matching events, my opinion is following. When you update something significant (ex. schedule type or start time) or when recurring schedule raises next job execution SQL Agent calculates next run time for the schedule and stores it in sysjobschedules.next_run_date.

So, it always has a list of next time executions, and doesn't solve the problem "find the matching events for a specific date". And I think you should implement your system in the same way.

But if you insist on doing it in such manner we can think about T-SQL query for that.

UPDATE
Below the script that can help you with your task. At the moment it works for two types of schedule:
1. start once
2. start daily every N days, once at the day
As far as I understand from your comment you'll use them. You can add additional types as soon as you need them in the same manner (UNION ALL ... UNION ALL ...).

Function msdb_time_convert converts HHMMSS integer into TIME datatype.

CREATE FUNCTION msdb_time_convert (
    @int_time INT
)
RETURNS TIME(0)
AS 
BEGIN
    IF NOT (@int_time BETWEEN 0 AND 235959) 
        RETURN NULL

    DECLARE @str VARCHAR(32) = CAST(@int_time AS VARCHAR(32))
    SELECT @str = REPLICATE('0', 6 - LEN(@str)) + @str
    SELECT @str = STUFF(@str, 3, 0, ':')
    SELECT @str = STUFF(@str, 6, 0, ':')

    RETURN CONVERT(TIME(0), @str, 108)
END
GO

You can specify any datetime into @find_date variable to find next runs at different points of time.

DECLARE @find_date DATETIME = GETDATE()

;WITH CTE AS (
    SELECT 
        CONVERT(DATE, CAST(active_start_date AS VARCHAR(32)), 112) AS active_start_date, 
        CONVERT(DATE, CAST(active_end_date AS VARCHAR(32)), 112) AS active_end_date, 
        dbo.msdb_time_convert(active_start_time) AS active_start_time,
        dbo.msdb_time_convert(active_end_time) AS active_end_time,  
        schedule_id,
        schedule_uid,
        name,
        enabled,
        freq_type,
        freq_interval,
        freq_subday_type,
        freq_subday_interval,
        freq_relative_interval,
        freq_recurrence_factor,
        CAST(@find_date AS DATE) AS today_date,
        CAST(@find_date AS TIME(0)) AS today_time,
        DATEADD(day, DATEDIFF(day, CONVERT(DATETIME, CAST(active_start_date AS VARCHAR(32)), 112) - 1, @find_date) % NULLIF(freq_interval, 0), CAST(@find_date AS DATE)) AS next_daily_day
    FROM dbo.sysschedules
)
SELECT 
    schedule_id,
    name,
    CAST(active_start_date AS DATETIME) + active_start_time AS next_run_datetime
FROM CTE
WHERE
    enabled = 1
    AND freq_type = 1       -- 1 = One time only
    AND CAST(active_start_date AS DATETIME) + active_start_time >= @find_date

UNION ALL

SELECT 
    schedule_id,
    name,
    DATEADD(
        DAY,
        CASE WHEN CAST(next_daily_day AS DATETIME) + active_start_time > @find_date THEN 0 ELSE freq_interval END,
        CAST(next_daily_day AS DATETIME) + active_start_time 
    ) AS next_run_datetime
FROM CTE 
WHERE
    enabled = 1
    AND freq_type = 4                   -- 4 = Daily (Every freq_interval days)
    AND freq_subday_type = 1            -- 1 = At the specified time
    AND CAST(active_end_date AS DATETIME) + active_end_time >= @find_date