1

I have been tasked with creating an events calendar for a website which will be backed by SQL Server. Using some of the information listed at: Calendar Recurring/Repeating Events - Best Storage Method I have created two tables in the database;

CREATE TABLE [Calendar].[Event](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](100) NOT NULL,
    [Details] [nvarchar](max) NOT NULL,
    [EventType] [int] NOT NULL,
    [Dismissable] [bit] NOT NULL DEFAULT ((1)),
    [Created] [datetime2](7) NOT NULL DEFAULT (getdate()),
    [CreatedBy] [uniqueidentifier] NOT NULL,
    [LastEdited] [datetime2](7) NOT NULL DEFAULT (getdate()),
    [EditedBy] [uniqueidentifier] NOT NULL
)

CREATE TABLE [Calendar].[EventSchedule](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EventId] [int] NOT NULL,
    [StartDate] [datetime2](7) NOT NULL,
    [EndDate] [datetime2](7) NULL,
    [IntervalDays] [int] NULL,
    [IntervalWeeks] [int] NULL,
    [IntervalMonths] [int] NULL,
    [IntervalYears] [int] NULL,
    [WeekDayNumber] [int] NULL,
    [MonthWeekNumber] [int] NULL,
    [YearMonthNumber] [int] NULL
)

I am now attempting to write a stored procedure to capture all events (singular and recurring) that fall between a date range, but cannot get my head around a catch all solution that doesn't involve generating temporary tables or cursors.

My current roadblock is the events that recur every n days. So far I have the following:

CREATE PROCEDURE Calendar.GetEventsForDateRange
@StartDateRange DATETIME2
@EndDateRange DATETIME2

AS
BEGIN

SELECT * --for brevity
FROM Calendar.EventSchedule
WHERE (StartDate <= @EndDateRange
AND DATEDIFF(DAY, StartDate, @EndDateRange) / IntervalDays >= 1)
OR (StartDate >= @StartDateRange AND StartDate <= @EndDateRange)

END

I have an event where StartDate = 2017-07-02 and IntervalDays = 7. If I pass a @StartDateRange = 2017-07-03 and @EndDateRange = 2017-07-09 the event is correctly returned. If I then change the @StartDateRange = 2017-07-13 and @EndDateRange = 2017-07-15 the event is still returned, where its next occurrence should be 2017-07-16.

How would I alter/amend my current stored procedure to return events that will only occur within a date range?

Any help on this would be greatly appreciated.

bowfinger
  • 45
  • 2
  • 9

0 Answers0