比较DateTime找到可用插槽的结构
我想搜索列表中所有用户的事件，并搜索所有时间，每个用户在7 AM-7PM没有30分钟或更长时间。然而，有一个陷阱，如果某种方法被标记为重复，即位重复设置为1，则事件将在开始后重复52周（因此时间不可用）。在存储过程中检索这些事件。
到目前为止，我的代码如下。我想以正确的方式编写这个过程吗？我不确定如何继续按照我的意愿返回函数。有人能帮我吗？
List usernames = //List of usernames.DateTime start = //DateTime for start of period you would like to schedule meetingDateTime 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 startTimes; List表结构：
DECLARE @Users TABLE( UserID INT IDENTITY Username VARCHAR(32));DECLARE @Groups TABLE( GroupID INT IDENTITY GroupName VARCHAR(32));DECLARE @Membership TABLE( UserIDINT, GroupID INT);DECLARE @event TABLE( event_id INT IDENTITY event_start DATETIME, event_end DATETIME, group_id INT, recurring BIT);我想要的功能示例:
用户将数据库中的多个用户添加到列表中。用户选择他想要与所有这些用户见面的时间段。我的算法计算了所有用户的所有空闲时间（即所有用户之间的会议和时间）>
30mins的时间）。
附加信息 ：
样例：
用户A尝试组织和用户B会议。所有时间段都是免费的。我希望算法能返回30分钟以上，并且能返回30分钟以上==所有可能的组合开始时间和结束时间（参数）的持续时间DateTime开始和DateTime结束。
用户A计划从下午6点到晚上7点以外的所有活动。他试图与用户合作。B组织一个小时的会议。B没有组织任何活动-返回DateTime 6PM和DateTime 7pm指示会议的开始和结束。
重复：用户A周一下午5点到下午6点发生重复事件。他试图在六周内的一周一组织一次两个小时的会议。DateTime开始和DateTime结束时相差2小时的所有组合。时间5 pm-7pm因为事件是重复的，每周发生52周。
以下是在设定的时间段(开始、结束)检索所有用户事件的存储过程:
ALTER PROCEDURE dbo.GetEvents @UserName VARCHAR(50),@StartDate DATETIME,@EndDate DATETIME ASBEGIN -- DEFINE A CTE TO GET ALL GROUPS ASSOCIATED WITH THE CURRENT USER ;WITH Groups AS(SELECTGroupID FROM Membershipm 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 ( SELECTe.* FROM event e INNER JOIN Groups m ON m.GroupID = e.group_id UNION ALL SELECTe.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 SELECTROW_NUMBER() OVER (ORDER BY Object_ID) AS weeks FROM SYS.OBJECTS AS w WHEREe.recurring = 1 ) -- GET ALL EVENTS WHERE THE EVENTS FALL IN THE PERIOD DEFINED SELECT* FROM AllEvents WHERE Event_Start >= @StartDate AND Event_End 因此，想象一些表：
USE tempdb;GOCREATE TABLE dbo.Users( UserID INT IDENTITY Username VARCHAR(32));CREATE TABLE dbo.Groups( GroupID INT IDENTITY GroupName VARCHAR(32));CREATE TABLE dbo.Membership( UserIDINT, GroupID INT);CREATE TABLE dbo.( event_id INT IDENTITY event_start DATETIME, event_end DATETIME, group_id INT, recurring BIT);想象一些示例数据并不难提供：
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_start,event_end,group_id,recurring)-- user A,almost all day meeting on a specific dateSELECT1、00-- user A,recurring meeting every MondayUNION ALL SELECT 1、1-- user A,recurring meeting every Tuesday (future)UNION ALL SELECT 20120327 14:00，20120327 15:00，1，1GO现在我们可以构建这个存储过程：
CREATE PROCEDURE dbo.GetPossibleMeetingTimes @AskingUserID INT, @TargetUserID INT, @Duration INT, -- in minutes! @StartDate SMALLDATETIME,-- assumes date,no time! @EndDate SMALLDATETIME-- again - date,no time!ASBEGIN 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 ) 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 )-1 FROM sys.objects) AS x CROSS JOIN dRange ) SELECT p.ds,p.de FROM possible AS p WHERE p.de = @StartDate AND event_start = 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 > sub.event_start AND p.ds 示例调用：
-- 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';结果：
https://i.stack.imgur.com/UIqSW.png
-- 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';结果：
https://i.stack.imgur.com/mrugp.png
-- 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 PMEXEC dbo.GetPossibleMeetingTimes @AskingUserID = 1, @TargetUserID = 2, @Duration = @StartDate = '20120319',-- user A has a recurring meeting @EndDate = '20120319';结果：
https://i.stack.imgur.com/QrESh.png
现在请注意，我已经照顾了一些你没有考虑或提到的因素（例如，未来的重复事件）。另一方面，我没有处理其他因素（如夏季系统，如果可能影响夏季系统），也没有测试所有可能的情况（例如，一天中已经存在的多个事件）。
如果你通过了一定的范围(比如2012)，我确实测试过-03-12->
2012-03-14）事实上，您将收集上述结果，并且提供的间隙大致相同（这些时间因持续时间而异）。重要的是，停电时间是实现的。我没有对未来重复事件的日期范围进行逻辑测试，包括事件的第一个例子前后的工作日。
如果任何情况都不适合你，那么这就是为什么重要的是你要 使用示例数据 向我们展示所有案例，而不是单词
并在给出数据时解释查询的预期结果。
编辑 -要处理超过2个用户，您只需要进行一些更改。若添加拆分功能，如下：
CREATE FUNCTION dbo.SplitInts( @List VARCHAR(MAX) )RETURNS TABLEAS RETURN ( SELECT Item = CONVERT(INT,Item) FROM ( SELECT Item = x.i.value('(./text()INT') FROM ( SELECT = CONVERT(XML,'' REPLACE(@List,',',' ').query('.')) AS a CROSS APPLY .nodes('i') AS x(i)) AS y WHERE Item IS NOT NULL );现在，存储过程发生了很小的变化（我省略了未更改的位置）：
ALTER PROCEDURE dbo.GetPossibleMeetingTimes @UserIDList VARCHAR(MAX), -- removed other two parameters @Duration INT, @StartDate SMALLDATETIME, @EndDate SMALLDATETIMEAS... 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 > sub.event_start AND p.ds 因此，您的电话号码将稍微改为：
EXEC dbo.GetPossibleMeetingTimes @UserIDList = @Duration = 30， @StartDate= @EndDate = '20120314';只要确保请求包含在逗号分隔列表中。p>PS该附录未经测试。
页:
[1]