How can I store and query schedule data?

Question

I'd like to allow my users to setup a schedule for their events. It could be a single day, or for convenience I'd like to allow them to specify a reoccurring event (similar to an Outlook appointment).

For the single event it seems pretty easy (pseudo-code):

Just have a DateOfEvent column that has the date on it.

To grab future events:

Select * from events where DateOfEvent > {DateTime.Now}

But how could I store and query a reoccurring event? I don't need to do times, as I'd just store that seperately, and if they needed a different time I'd just have them create another event. So no: Every wednesday at 5 and thursdays at 3.

Examples:

Every mon, tues, wed, thu, fri, every week

Every wed every week

Every second tuesday of the month

What I used to query

I added a start and end dates. If the user selected a single date I set both start and end dates to the chosen date. I just had to modify the answer's code a bit.

DECLARE 
    @StartDate SMALLDATETIME, 
    @EndDate   SMALLDATETIME;

SELECT
    @StartDate = '20091129',
    @EndDate   = '20101220';

SELECT
    d.CurrentDate,
    m.*
FROM
    Calendar AS d
    INNER JOIN Meet AS m
    ON
    (
        (d.CurrentDate = m.StartDate AND d.CurrentDate = m.EndDate)
        OR d.DaysOfTheMonth = m.DayOfTheMonth
        OR (d.DaysOfTheWeek = m.DayOfTheWeek AND COALESCE(m.WeekOfTheMonth, d.WeekOfTheMonth) = d.WeekOfTheMonth)
        OR d.DaysOfTheWeek IN (1,7) AND m.OnWeekends = 1
        OR d.DaysOfTheWeek BETWEEN 2 AND 6 AND m.OnWeekDays = 1
    )
    -- now you can inner join to the event table
    -- to list the name, other details, etc.
    WHERE
        d.CurrentDate BETWEEN @StartDate AND @EndDate
        AND d.CurrentDate BETWEEN m.StartDate AND m.EndDate
        ORDER BY d.CurrentDate;

GO

Answer 1

We have created sql'server job, that periodically (looking at you claim hourly is enough) called some stored procedure. On other turn this procedure selects does 2 things:

1. calculate NEXT user event basing on some recursive info from table [A]. And place this event to table [B]
2. Selects ready to fire events from table [B]

Answer 2

You could do something like this:

CREATE TABLE dbo.EventSchedule
(
  EventID    INT,           -- FOREIGN KEY to event details
  EventDate  SMALLDATETIME, -- if one-time event
  DayOfMonth TINYINT,       -- if once a month, e.g. 3rd of every month
  DayOfWeek  TINYINT,       -- if once a week, e.g. every Tuesday = 3
  WeekDays   BIT,           -- if only on weekdays, e.g. 1 = mon-fri
  Weekends   BIT,           -- if only on weekends, e.g. 1 = sat-sun

  -- the next two are combined, e.g. 2/2 = 2nd Monday of each month
  MonthlyInstance TINYINT,
  MonthlyWeekday  TINYINT
);

So then if you wanted to find all the events scheduled to happen on a given date, you could do:

DECLARE 
  @dt   SMALLDATETIME,
  @dm   TINYINT,
  @dw   TINYINT,
  @inst TINYINT;

SELECT
  @dt   = '20091201',
  @dm   = DATEPART(DAY, @dt)
  @dw   = DATEPART(WEEKDAY, @dt),
  @inst = DATEDIFF(WEEK, DATEADD(DAY, 1-@dm, @dt), @dt) + 1;

SELECT EventID
  FROM dbo.EventSchedule
  WHERE EventDate = @dt
  OR DayOfMonth = @dm
  OR DayOfWeek = @dw
  OR (Weekdays = 1 AND @dw BETWEEN 2 AND 6)
  OR (Weekends = 1 AND @dw IN (1,7))
  OR (MonthlyInstance = @inst AND MonthlyWeekday = @dw);

This is untested (I am posting from a Mac on Thanksgiving, after all), and relies on SET DATEFIRST being the default (where Sunday = 1, Monday = 2, etc). I'm also not 100% confident on the 2nd Tuesday part, as it likely needs some additional math depending on the weekday of the first day of the month. But I thought this could give you a start for the other parts, and I'll come back and revisit when I have a chance.

For some much more complicated options, see: http://www.codeproject.com/KB/database/sqlscheduleselector.aspx

And here is a more complete example, with a populated calendar table, some sample events of different types, and a query that retrieves all of the expected events given a date range.

CREATE DATABASE test;
GO
USE test;
GO

SET NOCOUNT ON;
GO

CREATE TABLE dbo.Calendar
(
    dt SMALLDATETIME PRIMARY KEY,
    dm TINYINT,
    dw TINYINT,
    mw TINYINT,
    wd BIT
);
GO

-- populate the table with the days from 2009
-- (you will want more obviously; change TOP or add WHERE)

INSERT dbo.Calendar(dt, dm, dw, mw, wd)
  SELECT
    dt, dm, dw, 
    mw = DATEDIFF(WEEK, DATEADD(DAY, 1-dm, dt), dt) + 1,
    CASE WHEN dw IN (1, 7) THEN 0 ELSE 1 END
    FROM 
    (
      SELECT
     dt,
     dm = DATEPART(DAY, dt),
     dw = DATEPART(WEEKDAY, dt)
      FROM 
      (
     SELECT dt = DATEADD(DAY, n-1, '20090101')
     FROM
     (
       SELECT TOP 365 
      n = ROW_NUMBER() OVER (ORDER BY c1.[object_id])
      FROM sys.all_columns AS c1
      CROSS JOIN sys.all_columns AS c2
      ORDER BY n
     ) AS x) AS y) AS z;
GO

-- create your schedule table:

CREATE TABLE dbo.EventSchedule
(
  EventID      INT,           -- FOREIGN KEY to event details
  EventDate    SMALLDATETIME, -- if one-time event
  [DayOfMonth] TINYINT,       -- if once a month, e.g. 3rd of every month
  [DayOfWeek]  TINYINT,       -- if once a week, e.g. every Tuesday = 3
  Weekdays     BIT,           -- if only on weekdays, e.g. 1 = mon-fri
  Weekends     BIT,           -- if only on weekends, e.g. 1 = sat-sun
                              -- if you want every day, set Weekdays+Weekends = 1
  WeekOfMonth  TINYINT        -- if only the nth Tuesday etc.
);

-- create some events:
INSERT dbo.EventSchedule
(
    EventID,
    EventDate,
    [DayOfMonth],
    [DayOfWeek],
    Weekdays,
    Weekends,
    WeekOfMonth
)
-- one on Jan 5th:
    SELECT 1, '20090105', NULL, NULL, NULL, NULL, NULL

-- one on the 3rd of every month:
    UNION ALL SELECT 2, NULL, 3, NULL, NULL, NULL, NULL

-- one every Tuesday:
    UNION ALL SELECT 3, NULL, NULL, 3, NULL, NULL, NULL

-- one the 2nd Wednesday of each month:
    UNION ALL SELECT 4, NULL, NULL, 4, NULL, NULL, 2

-- one only on weekends:
    UNION ALL SELECT 5, NULL, NULL, NULL, NULL, 1, NULL

-- one only on weekdays:
    UNION ALL SELECT 6, NULL, NULL, NULL, 1, NULL, NULL;

-- now, we have 6 events, some of which will happen 
-- multiple times in any given date range.  So let's
-- try it:

DECLARE 
    @StartDate SMALLDATETIME, 
    @EndDate   SMALLDATETIME;

SELECT
    @StartDate = '20090101',
    @EndDate   = '20090108';

SELECT
    d.dt,
    e.EventID
FROM
    dbo.Calendar AS d
    INNER JOIN dbo.EventSchedule AS e
    ON
    (
     d.dt = e.EventDate
     OR d.dm = e.[DayOfMonth]
     OR (d.dw = e.[DayOfWeek] AND COALESCE(e.WeekOfMonth, d.mw) = d.mw)
     OR d.dw IN (1,7) AND e.Weekends = 1
     OR d.dw BETWEEN 2 AND 6 AND e.Weekdays = 1
    )
    -- now you can inner join to the event table
    -- to list the name, other details, etc.
    WHERE
     d.dt BETWEEN @StartDate AND @EndDate
     ORDER BY d.dt, e.EventID;

GO

DROP TABLE dbo.Calendar, dbo.EventSchedule;
GO

USE [master];
GO

DROP DATABASE test;
GO

Answer 3

For storing the schedule, take a look at my answer in this question

http://stackoverflow.com/questions/1579638/what-is-the-best-way-to-represent-recurring-events-in-database/1579661#1579661

Here I describe how SQL Server stores schedule information in their sysschedules and related tables. They have a really nice schema design which supports all the scheduling cases you are asking about. The documentation on MSDN is also very helpful.

To get all the schedule dates between a date range for outlook type display, take a look at Peter Larsson's blog, he created an excellent sproc to calculate these values.

  CREATE PROCEDURE dbo.uspGetScheduleTimes
  (
    @startDate DATETIME,
    @endDate DATETIME
  )
  AS
  /*
  This code is blogged here
  http://weblogs.sqlteam.com/peterl/archive/2008/10/10/Keep-track-of-all-your-jobs-schedules.aspx
  */
  SET NOCOUNT ON

  -- Create a tally table. If you already have one of your own please use that instead.
  CREATE TABLE #tallyNumbers
     (
    num SMALLINT PRIMARY KEY CLUSTERED
     )

  DECLARE       @index SMALLINT

  SET    @index = 1

  WHILE @index <= 8640
    BEGIN
     INSERT #tallyNumbers
    (
   num
    )
     VALUES (
   @index
    )

     SET    @index = @index + 1
    END

  -- Create a staging table for jobschedules
  CREATE TABLE #jobSchedules
     (
    rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    serverName SYSNAME NOT NULL,
    jobName SYSNAME NOT NULL,
    jobDescription NVARCHAR(512) NOT NULL,
    scheduleName SYSNAME NOT NULL,
    scheduleID INT NOT NULL,
    categoryName SYSNAME NOT NULL,
    freq_type INT NOT NULL,
    freq_interval INT NOT NULL,
    freq_subday_type INT NOT NULL,
    freq_subday_interval INT NOT NULL,
    freq_relative_interval INT NOT NULL,
    freq_recurrence_factor INT NOT NULL,
    startDate DATETIME NOT NULL,
    startTime DATETIME NOT NULL,
    endDate DATETIME NOT NULL,
    endTime DATETIME NOT NULL,
    jobEnabled INT NOT NULL,
    scheduleEnabled INT NOT NULL
     )

  /*
  -- Popoulate the staging table for JobSchedules with SQL Server 2000
  INSERT        #jobSchedules
     (
    serverName,
    jobName,
    jobDescription,
    scheduleName,
    scheduleID,
    categoryName,
    freq_type,
    freq_interval,
    freq_subday_type,
    freq_subday_interval,
    freq_relative_interval,
    freq_recurrence_factor,
    startDate,
    startTime,
    endDate,
    endTime,
    jobEnabled,
    scheduleEnabled
     )
  SELECT        sj.originating_server,
     sj.name,
     COALESCE(sj.description, ''),
     sjs.name,
     sjs.schedule_id,
     sc.name,
     sjs.freq_type,
     sjs.freq_interval,
     sjs.freq_subday_type,
     sjs.freq_subday_interval,
     sjs.freq_relative_interval,
     sjs.freq_recurrence_factor,
     COALESCE(STR(sjs.active_start_date, 8), CONVERT(CHAR(8), GETDATE(), 112)),
     STUFF(STUFF(REPLACE(STR(sjs.active_start_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'),
     STR(sjs.active_end_date, 8),
     STUFF(STUFF(REPLACE(STR(sjs.active_end_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'),
     sj.enabled,
     sjs.enabled
  FROM          msdb..sysjobschedules AS sjs
  INNER JOIN    msdb..sysjobs AS sj ON sj.job_id = sjs.job_id
  INNER JOIN    msdb..syscategories AS sc ON sc.category_id = sj.category_id
  WHERE         sjs.freq_type IN (1, 4, 8, 16, 32)
  ORDER BY      sj.originating_server,
     sj.name,
     sjs.name
  */

  -- Popoulate the staging table for JobSchedules with SQL Server 2005 and SQL Server 2008
  INSERT        #JobSchedules
     (
    serverName,
    jobName,
    jobDescription,
    scheduleName,
    scheduleID,
    categoryName,
    freq_type,
    freq_interval,
    freq_subday_type,
    freq_subday_interval,
    freq_relative_interval,
    freq_recurrence_factor,
    startDate,
    startTime,
    endDate,
    endTime,
    jobEnabled,
    scheduleEnabled
     )
  SELECT        srv.srvname,
     sj.name,
     COALESCE(sj.description, ''),
     ss.name,
     ss.schedule_id,
     sc.name,
     ss.freq_type,
     ss.freq_interval,
     ss.freq_subday_type,
     ss.freq_subday_interval,
     ss.freq_relative_interval,
     ss.freq_recurrence_factor,
     COALESCE(STR(ss.active_start_date, 8), CONVERT(CHAR(8), GETDATE(), 112)),
     STUFF(STUFF(REPLACE(STR(ss.active_start_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'),
     STR(ss.active_end_date, 8),
     STUFF(STUFF(REPLACE(STR(ss.active_end_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'),
     sj.enabled,
     ss.enabled
  FROM          msdb..sysschedules AS ss
  INNER JOIN    msdb..sysjobschedules AS sjs ON sjs.schedule_id = ss.schedule_id
  INNER JOIN    msdb..sysjobs AS sj ON sj.job_id = sjs.job_id
  INNER JOIN    sys.sysservers AS srv ON srv.srvid = sj.originating_server_id
  INNER JOIN    msdb..syscategories AS sc ON sc.category_id = sj.category_id
  WHERE         ss.freq_type IN(1, 4, 8, 16, 32)
  ORDER BY      srv.srvname,
     sj.name,
     ss.name

  -- Only deal with jobs that has active start date before @endDate
  DELETE
  FROM   #JobSchedules
  WHERE startDate > @endDate

  -- Only deal with jobs that has active end date after @startDate
  DELETE
  FROM   #JobSchedules
  WHERE endDate < @startDate

  -- Deal with first, second, third, fourth and last occurence
  DECLARE       @tempStart DATETIME,
    @tempEnd DATETIME

  SELECT @tempStart = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @startDate), '19000101'),
    @TempEnd = DATEADD(MONTH, DATEDIFF(MONTH, '18991231', @endDate), '18991231')

  CREATE TABLE #dayInformation
     (
    infoDate DATETIME PRIMARY KEY CLUSTERED,
    weekdayName VARCHAR(9) NOT NULL,
    statusCode INT NOT NULL,
    lastDay TINYINT DEFAULT 0
     )

  WHILE @tempStart <= @tempEnd
    BEGIN
     INSERT #dayInformation
    (
   infoDate,
   weekdayName,
   statusCode
    )
     SELECT @tempStart,
    DATENAME(WEEKDAY, @tempStart),
    CASE
   WHEN DATEPART(DAY, @tempStart) BETWEEN 1 AND 7 THEN 1
   WHEN DATEPART(DAY, @tempStart) BETWEEN 8 AND 14 THEN 2
   WHEN DATEPART(DAY, @tempStart) BETWEEN 15 AND 21 THEN 4
   WHEN DATEPART(DAY, @tempStart) BETWEEN 22 AND 28 THEN 8
   ELSE 0
    END

     SET    @tempStart = DATEADD(DAY, 1, @tempStart)
    END

  UPDATE        di
  SET           di.statusCode = di.statusCode + 16
  FROM          #dayInformation AS di
  INNER JOIN    (
    SELECT        DATEDIFF(MONTH, '19000101', infoDate) AS theMonth,
    DATEPART(DAY, MAX(infoDate)) - 6 AS theDay
    FROM          #dayInformation
    GROUP BY      DATEDIFF(MONTH, '19000101', infoDate)
     ) AS x ON x.theMonth = DATEDIFF(MONTH, '19000101', di.infoDate)
  WHERE         DATEPART(DAY, di.infoDate) >= x.theDay

  UPDATE        di
  SET           di.lastDay = 16
  FROM          #dayInformation AS di
  INNER JOIN    (
    SELECT        DATEDIFF(MONTH, '19000101', infoDate) AS theMonth,
    MAX(infoDate) AS theDay
    FROM          #dayInformation
    GROUP BY      DATEDIFF(MONTH, '19000101', infoDate)
     ) AS x ON x.theMonth = DATEDIFF(MONTH, '19000101', di.infoDate)
  WHERE         di.infoDate = x.theDay

  UPDATE #dayInformation
  SET    lastDay = DATEPART(DAY, infoDate)
  WHERE DATEPART(DAY, infoDate) BETWEEN 1 AND 4

  -- Stage all individual schedule times
  CREATE TABLE #scheduleTimes
     (
    rowID INT NOT NULL,
    infoDate DATETIME NOT NULL,
    startTime DATETIME NOT NULL,
    endTime DATETIME NOT NULL,
    waitSeconds INT DEFAULT 0
     )

  CREATE CLUSTERED INDEX IX_rowID ON #scheduleTimes(rowID)

  -- Insert one time only schedules
  INSERT #scheduleTimes
    (
     rowID,
     infoDate,
     startTime,
     endTime
    )
  SELECT rowID,
    startDate,
    startTime,
    endTime
  FROM   #jobSchedules
  WHERE freq_type = 1
    AND startDate >= @StartDate
    AND startDate <= @EndDate

  -- Insert daily schedules
  INSERT        #scheduleTimes
     (
    rowID,
    infoDate,
    startTime,
    endTime,
    waitSeconds
     )
  SELECT        js.rowID,
     di.infoDate,
     js.startTime,
     js.endTime,
     CASE js.freq_subday_type
    WHEN 1 THEN 0
    WHEN 2 THEN js.freq_subday_interval
    WHEN 4 THEN 60 * js.freq_subday_interval
    WHEN 8 THEN 3600 * js.freq_subday_interval
     END
  FROM          #jobSchedules AS js
  INNER JOIN    #dayInformation AS di ON di.infoDate >= @startDate
    AND di.infoDate <= @endDate
  WHERE         js.freq_type = 4
     AND DATEDIFF(DAY, js.startDate, di.infoDate) % js.freq_interval = 0

  -- Insert weekly schedules
  INSERT        #scheduleTimes
     (
    rowID,
    infoDate,
    startTime,
    endTime,
    waitSeconds
     )
  SELECT        js.rowID,
     di.infoDate,
     js.startTime,
     js.endTime,
     CASE js.freq_subday_type
    WHEN 1 THEN 0
    WHEN 2 THEN js.freq_subday_interval
    WHEN 4 THEN 60 * js.freq_subday_interval
    WHEN 8 THEN 3600 * js.freq_subday_interval
     END
  FROM          #jobSchedules AS js
  INNER JOIN    #dayInformation AS di ON di.infoDate >= @startDate
    AND di.infoDate <= @endDate
  WHERE         js.freq_type = 8
     AND 1 =       CASE
   WHEN js.freq_interval & 1 = 1 AND di.weekdayName = 'Sunday' THEN 1
   WHEN js.freq_interval & 2 = 2 AND di.weekdayName = 'Monday' THEN 1
   WHEN js.freq_interval & 4 = 4 AND di.weekdayName = 'Tuesday' THEN 1
   WHEN js.freq_interval & 8 = 8 AND di.weekdayName = 'Wednesday' THEN 1
   WHEN js.freq_interval & 16 = 16 AND di.weekdayName = 'Thursday' THEN 1
   WHEN js.freq_interval & 32 = 32 AND di.weekdayName = 'Friday' THEN 1
   WHEN js.freq_interval & 64 = 64 AND di.weekdayName = 'Saturday' THEN 1
   ELSE 0
    END
     AND(DATEDIFF(DAY, js.startDate, di.infoDate) / 7) % js.freq_recurrence_factor = 0

  -- Insert monthly schedules
  INSERT        #scheduleTimes
     (
    rowID,
    infoDate,
    startTime,
    endTime,
    waitSeconds
     )
  SELECT        js.rowID,
     di.infoDate,
     js.startTime,
     js.endTime,
     CASE js.freq_subday_type
    WHEN 1 THEN 0
    WHEN 2 THEN js.freq_subday_interval
    WHEN 4 THEN 60 * js.freq_subday_interval
    WHEN 8 THEN 3600 * js.freq_subday_interval
     END
  FROM          #jobSchedules AS js
  INNER JOIN    #dayInformation AS di ON di.infoDate >= @startDate
    AND di.infoDate <= @endDate
  WHERE         js.freq_type = 16
     AND DATEPART(DAY, di.infoDate) = js.freq_interval
     AND DATEDIFF(MONTH, js.startDate, di.infoDate) % js.freq_recurrence_factor = 0

  -- Insert monthly relative schedules
  INSERT        #scheduleTimes
     (
    rowID,
    infoDate,
    startTime,
    endTime,
    waitSeconds
     )
  SELECT        js.rowID,
     di.infoDate,
     js.startTime,
     js.endTime,
     CASE js.freq_subday_type
    WHEN 1 THEN 0
    WHEN 2 THEN js.freq_subday_interval
    WHEN 4 THEN 60 * js.freq_subday_interval
    WHEN 8 THEN 3600 * js.freq_subday_interval
     END
  FROM          #jobSchedules AS js
  INNER JOIN    #dayInformation AS di ON di.infoDate >= @startDate
    AND di.infoDate <= @endDate
  WHERE         js.freq_type = 32
     AND 1 =       CASE
   WHEN js.freq_interval = 1 AND di.weekdayName = 'Sunday' THEN 1
   WHEN js.freq_interval = 2 AND di.weekdayName = 'Monday' THEN 1
   WHEN js.freq_interval = 3 AND di.weekdayName = 'Tuesday' THEN 1
   WHEN js.freq_interval = 4 AND di.weekdayName = 'Wednesday' THEN 1
   WHEN js.freq_interval = 5 AND di.weekdayName = 'Thursday' THEN 1
   WHEN js.freq_interval = 6 AND di.weekdayName = 'Friday' THEN 1
   WHEN js.freq_interval = 7 AND di.weekdayName = 'Saturday' THEN 1
   WHEN js.freq_interval = 8 AND js.freq_relative_interval = di.lastDay THEN 1
   WHEN js.freq_interval = 9 AND di.weekdayName NOT IN('Sunday', 'Saturday') THEN 1
   WHEN js.freq_interval = 10 AND di.weekdayName IN('Sunday', 'Saturday') THEN 1
   ELSE 0
    END
     AND di.statusCode & js.freq_relative_interval = js.freq_relative_interval
     AND DATEDIFF(MONTH, js.startDate, di.infoDate) % js.freq_recurrence_factor = 0

  -- Get the daily recurring schedule times
  INSERT        #scheduleTimes
     (
    rowID,
    infoDate,
    startTime,
    endTime,
    waitSeconds
     )
  SELECT        st.rowID,
     st.infoDate,
     DATEADD(SECOND, tn.num * st.waitSeconds, st.startTime),
     st.endTime,
     st.waitSeconds
  FROM          #scheduleTimes AS st
  CROSS JOIN    #tallyNumbers AS tn
  WHERE         tn.num * st.waitSeconds <= DATEDIFF(SECOND, st.startTime, st.endTime)
     AND st.waitSeconds > 0

  -- Present the result
  SELECT        js.scheduleID,
     js.serverName,
     js.jobName,
     js.jobDescription,
     js.scheduleName,
     js.categoryName,
     st.infoDate,
     st.startTime,
     st.endTime,
     js.jobEnabled,
     js.scheduleEnabled
  FROM          #scheduleTimes AS st
  INNER JOIN    #jobSchedules AS js ON js.rowID = st.rowID

  -- Clean up
  DROP TABLE    #jobSchedules,
     #dayInformation,
     #scheduleTimes,
     #tallyNumbers

For getting a meaningful short description of the schedule like "Every Monday at 2:00pm", take a look at the sp_get_schedule_description sproc.