Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>So imagine some tables:</p> <pre class="lang-sql prettyprint-override"><code>USE tempdb; GO CREATE TABLE dbo.Users ( UserID INT IDENTITY(1,1), Username VARCHAR(32) ); CREATE TABLE dbo.Groups ( GroupID INT IDENTITY(1,1), GroupName VARCHAR(32) ); CREATE TABLE dbo.Membership ( UserID INT, GroupID INT ); CREATE TABLE dbo.[event] ( event_id INT IDENTITY(1,1), event_start DATETIME, event_end DATETIME, group_id INT, recurring BIT ); </code></pre> <p>And imagine that some sample data weren't so difficult to provide:</p> <pre class="lang-sql prettyprint-override"><code>INSERT dbo.Users(Username) SELECT 'User A' UNION ALL SELECT 'User B'; INSERT dbo.Groups(GroupName) SELECT 'Group 1' UNION ALL SELECT 'Group 2'; INSERT dbo.Membership(UserID, GroupID) SELECT 1,1 UNION ALL SELECT 2,2; INSERT dbo.[event](event_start, event_end, group_id, recurring) -- user A, almost all day meeting on a specific date SELECT '20120313 07:00', '20120313 18:00', 1, 0 -- user A, recurring meeting every Monday UNION ALL SELECT '20120312 17:00', '20120312 18:00', 1, 1 -- user A, recurring meeting every Tuesday (future) UNION ALL SELECT '20120327 14:00', '20120327 15:00', 1, 1; GO </code></pre> <p>Now we can build this stored procedure:</p> <pre class="lang-sql prettyprint-override"><code>CREATE PROCEDURE dbo.GetPossibleMeetingTimes @AskingUserID INT, @TargetUserID INT, @Duration INT, -- in minutes! @StartDate SMALLDATETIME, -- assumes date, no time! @EndDate SMALLDATETIME -- again - date, no time! AS BEGIN SET NOCOUNT ON; ;WITH dRange(d) AS ( -- get the actual dates in the requested range -- limited to number of rows in sys.objects SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate)+1) DATEADD(DAY, n-1, @StartDate) FROM (SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.objects) AS x ), possible(ds, de) AS ( -- get all the timeslots of @Duration minutes -- between 7:00 AM and 7:00 PM for each day in -- the range - these are all *potential* slots SELECT DATEADD(MINUTE, 30*rn, DATEADD(HOUR, 7, dRange.d)), DATEADD(MINUTE, 30*rn + @Duration, DATEADD(HOUR, 7, dRange.d)) FROM (SELECT TOP (720/30) rn = ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.objects) AS x CROSS JOIN dRange ) SELECT p.ds, p.de FROM possible AS p WHERE p.de &lt;= DATEADD(HOUR, 19, DATEADD(DAY, DATEDIFF(DAY, 0, p.de), 0)) AND NOT EXISTS ( SELECT 1 FROM ( -- filter down to users with events on the days in the range SELECT group_id, event_start, event_end FROM dbo.[event] WHERE event_start &gt;= @StartDate AND event_start &lt; DATEADD(DAY, 1, @EndDate) UNION ALL -- also include users with recurring events on same weekday(s) -- normalized to the matching day in the range SELECT group_id, event_start = DATEADD(DAY, DATEDIFF(DAY, event_start, p.ds), event_start), event_end = DATEADD(DAY, DATEDIFF(DAY, event_end, p.ds), event_end) FROM dbo.[event] WHERE recurring = 1 AND event_start &lt;= DATEADD(DAY, 1, @EndDate) -- ignore future events AND event_start &gt;= DATEADD(WEEK, -52, @EndDate) -- 52 weeks out AND DATEDIFF(DAY, event_start, p.ds) % 7 = 0 -- same weekday ) AS sub WHERE sub.group_id IN ( -- this checks that events are within previously scheduled times SELECT GroupID FROM dbo.Membership WHERE UserID IN (@AskingUserID, @TargetUserID) AND (p.de &gt; sub.event_start AND p.ds &lt; sub.event_end) ) ) ORDER BY p.ds, p.de; END GO </code></pre> <p>Example calls:</p> <pre class="lang-sql prettyprint-override"><code>-- Case 1: User A tries to meet with User B on a day where -- both schedules are clear. EXEC dbo.GetPossibleMeetingTimes @AskingUserID = 1, @TargetUserID = 2, @Duration = 30, @StartDate = '20120314', -- no events for either user @EndDate = '20120314'; </code></pre> <p>Results:</p> <p><img src="https://i.stack.imgur.com/UIqSW.png" alt="no events for either user"></p> <pre class="lang-sql prettyprint-override"><code>-- Case 2: User A tries to meet with User B for an hour, on -- a day where user A has meetings from 7 AM to 6 PM. EXEC dbo.GetPossibleMeetingTimes @AskingUserID = 1, @TargetUserID = 2, @Duration = 60, @StartDate = '20120313', -- user A has an almost all-day event @EndDate = '20120313'; </code></pre> <p>Results:</p> <p><img src="https://i.stack.imgur.com/mrugp.png" alt="user A is busy almost all day"></p> <pre class="lang-sql prettyprint-override"><code>-- Case 3: User A tries to meet with User B for two hours, on -- a weekday where User A has a recurring meeting from 5-6 PM EXEC dbo.GetPossibleMeetingTimes @AskingUserID = 1, @TargetUserID = 2, @Duration = 120, @StartDate = '20120319', -- user A has a recurring meeting @EndDate = '20120319'; </code></pre> <p>Results:</p> <p><img src="https://i.stack.imgur.com/QrESh.png" alt="user A has a recurring meeting"></p> <p>Now note that I took care of several factors you either haven't considered or haven't mentioned (such as a recurring event that starts in the future). On the other hand, I also didn't deal with some other factors (e.g. daylight savings time, if it may impact this at all) and didn't test all possible scenarios (e.g. multiple events on the same day that already exist). </p> <p>I did test that if you pass in a range (e.g. 2012-03-12 -> 2012-03-14) you will essentially just get a union of the above results with roughly the same time slots made available (these vary based on duration of course). The important part is that the blackout timeslots are honored. I did not test the logic for the case where a recurring event starts in the future and the provided date range includes that weekday both before and after the first instance of the event.</p> <p>If any case doesn't work for you, then this is exactly why it's important that you show us all your cases <em>using sample data, not word problems</em> and also explain the desired results of the query given that data.</p> <p><strong>EDIT</strong> - to handle more than 2 users, you only need a few changes. If you add a split function as follows:</p> <pre class="lang-sql prettyprint-override"><code>CREATE FUNCTION dbo.SplitInts( @List VARCHAR(MAX) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = x.i.value('(./text())[1]', 'INT') FROM ( SELECT [XML] = CONVERT(XML, '&lt;i&gt;' + REPLACE(@List, ',', '&lt;/i&gt;&lt;i&gt;') + '&lt;/i&gt;').query('.')) AS a CROSS APPLY [XML].nodes('i') AS x(i)) AS y WHERE Item IS NOT NULL ); </code></pre> <p>Now very minor changes to the stored procedure (I've left out the unchanged bits):</p> <pre class="lang-sql prettyprint-override"><code>ALTER PROCEDURE dbo.GetPossibleMeetingTimes @UserIDList VARCHAR(MAX), -- removed other two parameters @Duration INT, @StartDate SMALLDATETIME, @EndDate SMALLDATETIME AS ... WHERE sub.group_id IN -- changed the code within this subquery ( SELECT GroupID FROM dbo.Membership AS m INNER JOIN dbo.SplitInts(@UserIDList) AS i ON m.UserID = i.Item WHERE (p.de &gt; sub.event_start AND p.ds &lt; sub.event_end) ) ... </code></pre> <p>So then your call just changes slightly to:</p> <pre class="lang-sql prettyprint-override"><code>EXEC dbo.GetPossibleMeetingTimes @UserIDList = '1,2,3,4,5', @Duration = 30, @StartDate = '20120314', @EndDate = '20120314'; </code></pre> <p>Just make sure that the requester is included in the comma-separated list.</p> <p>PS this addendum is untested.</p>
    singulars
    1. This table or related slice is empty.
    1. POComparing DateTime structs to find free slots
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAaron Bertrand
    1. USAaron Bertrand
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POComparing DateTime structs to find free slots
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIn he *real* world you could probably charge someone for half a days work. Here you get a +1 from me :).
      singulars
      1. PO
      1. USMikael Eriksson
    2. COThanks Mikael. I'm starting to wonder if our profiles should have a PayPal link. :-)
      singulars
      1. PO
      1. USAaron Bertrand
    3. COWow, amazing answer! Thank you so much for this! This is incredible! I will put a bounty for all of my reputation on this question and accept your answer in two days so you get all of the reputation! That's incredible, I am flabbergasted!
      singulars
      1. PO
      1. USSimon Kiely
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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