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.