I am trying to write an SQL (Server) query which will return all events on a current day, and for all events where the column recurring
= 1, I want it to return this event on the day it is being held and for the subsequent 52 weeks following the event.
My tables are structured as followed :
Event
{
event_id (PK)
title,
description,
event_start DATETIME,
event_end DATETIME,
group_id,
recurring
}
Users
{
UserID (PK)
Username
}
Groups
{
GroupID (PK)
GroupName
}
Membership
{
UserID (FK)
GroupID (FK)
}
The code I have thus far is as follows :
var db = Database.Open("mPlan");
string username = HttpContext.Current.Request.Cookies.Get("mpUsername").Value;
var listOfGroups = db.Query("SELECT GroupID FROM Membership WHERE UserID = (SELECT UserID from Users WHERE Username = @0 )", username);
foreach(var groupID in listOfGroups)
{
int newGroupID = groupID.GroupID;
var result = db.Query(
@"SELECT e.event_id, e.title, e.description, e.event_start, e.event_end, e.group_id, e.recurring
FROM event e
JOIN Membership m ON m.GroupID = e.group_id
WHERE e.recurring = 0
AND m.GroupID = @0
AND e.event_start >= @1
AND e.event_end <= @2
UNION ALL
SELECT e.event_id, e.title, e.description, DATEADD(week, w.weeks, e.event_start), DATEADD(week, w.weeks, e.event_end), e.group_id, e.recurring
FROM event e
JOIN Membership m ON m.GroupID = e.group_id
CROSS JOIN
( SELECT row_number() OVER (ORDER BY Object_ID) AS weeks
FROM SYS.OBJECTS
) AS w
WHERE e.recurring = 1
AND m.GroupID = @3
AND DATEADD(WEEK, w.Weeks, e.event_start) >= @4
AND DATEADD(WEEK, w.Weeks, e.event_end) <= @5", newGroupID, start, end, newGroupID, start, end
);
This results in when one queries for the date of the event stored in the database, this event and 52 weeks of events are returned. When one queries for the event the week after this one, nothing is returned.
See Question&Answers more detail:
os