title: Comparing DateTime structs to find free slots
body: <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&lt;string&gt; 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&lt;DateTime&gt; startTimes;
      List&lt;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 &gt;= @StartDate 
  AND     Event_End &lt;= @EndDate 
  
  END 
  </code></pre>