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

Filling in the Calandar table:

INSERT dbo.Calendar(CurrentDate, DaysOfTheMonth, DaysOfTheWeek, WeekOfTheMonth, IsWeekDay, Even) 

    SELECT
    CurrentDate, DaysOfTheMonth, DaysOfTheWeek,
    WeekOfTheMonth = DATEDIFF(WEEK, DATEADD(DAY, 1-DaysOfTheMonth, CurrentDate), CurrentDate) + 1,
    CASE WHEN DaysOfTheWeek IN (1, 7) THEN 0 ELSE 1 END AS IsWeekDay,
    CASE WHEN w % 2 = 1 THEN 0 ELSE 1 END AS Even
    FROM 
    (
      SELECT
        CurrentDate,
        DaysOfTheMonth = DATEPART(DAY, CurrentDate),
        DaysOfTheWeek = DATEPART(WEEKDAY, CurrentDate),
        w = DATEPART(WEEK, CurrentDate)
      FROM 
      (
        SELECT CurrentDate = DATEADD(DAY, n-1, '20141001')
        FROM
        (
          SELECT TOP 900 
            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;

Answer 1

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