StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POComparing DateTime structs to find free slots
text
Body
copied!<p>I would like to search through the events of all of the users in a list and retrieve all times where every user is free of 30mins or greater between 7AM-7PM.</p> <p>There is a catch however, if a method is marked as 'recurring', ie the bit recurring is set to 1, then that event recurs for a period of 52 weeks after its beginning (so the time is not available). Retrieval of these events are taken care of in a stored procedure.</p> <p>My code so far is below. Am I going about writing this procedure the right way? I'm not really sure how to proceed to get the function to return as I would like. Would anyone be able to help me with this?</p> <pre><code>List<string> usernames = //List of usernames. DateTime start = //DateTime for start of period you would like to schedule meeting DateTime end = //DateTime for end of period //int mins = //duration of meeting (must be 30mins or greater) foreach (string username in usernames) { //retrieve events for this user var db = Database.Open("mPlan"); List<DateTime> startTimes; List<DateTime endTimes; // This stored procedure returns all events of a user in a given time period, // including recurring events. var record = db.Query("EXEC dbo.GetEvents @0, @1, @2", username, start, end); foreach(var record in result) { startTimes.Add(record.event_start); endTimes.Add(record.event_end); } // so now I have a list of all start times and end times of events // for one user and could save all this data in a list } </code></pre> <p>Table structure:</p> <pre class="lang-sql prettyprint-override"><code>DECLARE @Users TABLE ( UserID INT IDENTITY(1,1), Username VARCHAR(32) ); DECLARE @Groups TABLE ( GroupID INT IDENTITY(1,1), GroupName VARCHAR(32) ); DECLARE @Membership TABLE ( UserID INT, GroupID INT ); DECLARE @event TABLE ( event_id INT IDENTITY(1,1), event_start DATETIME, event_end DATETIME, group_id INT, recurring BIT ); </code></pre> <p><strong>Example of functionality I would like :</strong> </p> <p>User adds multiple users from the database to a list. User selects a time period over which he would like to have a meeting with all of these users. My algorithm computes all time periods that are free for all users (i.e a times that would be suitable to have a meeting between all users and are >30mins ).</p> <p><strong>Additional information :</strong></p> <p>Sample cases : </p> <ul> <li><p>User A attempts to organize a meeting with User B. All timeslots are free. I would like the algorithm to return a DateTime start and DateTime end of all possible combinations of start times and end times that are >30mins and == duration ( a parameter ).</p></li> <li><p>Typical case : User A has events planned for all times except 6pm - 7pm. He attempts to organize a meeting with user B for duration of 1 hour. User B has no events organized - the DateTime 6PM and DateTime 7pm are returned to indicate the start and end time of meetings.</p></li> <li><p>Recurring case : User A has a recurring event at 5pm-6pm on a Monday. He tries to organize a meeting of 2 hours on a monday in six weeks time. All combinations of DateTime start and DateTime end where there is a difference of 2 hours are returned. The time 5pm-7pm is not returned, since this event is recurring and occurs every week for 52 weeks.</p></li> </ul> <p>Here is the stored procedure which retrieves all of a users events for a set time period (start, end):</p> <pre class="lang-sql prettyprint-override"><code>ALTER PROCEDURE dbo.GetEvents @UserName VARCHAR(50), @StartDate DATETIME, @EndDate DATETIME AS BEGIN -- DEFINE A CTE TO GET ALL GROUPS ASSOCIATED WITH THE CURRENT USER ;WITH Groups AS ( SELECT GroupID FROM Membership m INNER JOIN Users u ON m.UserID = u.UserID WHERE Username = @UserName GROUP BY GroupID ), -- DEFINE A CTE TO GET ALL EVENTS FOR THE GROUPS DEFINED ABOVE AllEvents AS ( SELECT e.* FROM event e INNER JOIN Groups m ON m.GroupID = e.group_id 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 INNER JOIN Groups 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 ) -- GET ALL EVENTS WHERE THE EVENTS FALL IN THE PERIOD DEFINED SELECT * FROM AllEvents WHERE Event_Start >= @StartDate AND Event_End <= @EndDate END </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload