比较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( UserID  INT, 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  (  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 因此，想象一些表：
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( UserID  INT, GroupID INT);CREATE TABLE dbo.[event]( 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](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 [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 = @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';结果：

-- 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';结果：

-- 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';结果：

现在请注意，我已经照顾了一些你没有考虑或提到的因素（例如，未来的重复事件）。另一方面，我没有处理其他因素（如夏季系统，如果可能影响夏季系统），也没有测试所有可能的情况（例如，一天中已经存在的多个事件）。
如果你通过了一定的范围(比如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()[1]INT') FROM (    SELECT [XML] = CONVERT(XML,'' REPLACE(@List,',','    ').query('.')) AS a CROSS APPLY [XML].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    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 > sub.event_start AND p.ds 因此，您的电话号码将稍微改为：
EXEC dbo.GetPossibleMeetingTimes    @UserIDList = @Duration = 30， @StartDate  = @EndDate = '20120314';只要确保请求包含在逗号分隔列表中。p>

S该附录未经测试。

比较DateTime找到可用插槽的结构

妙有真空 LV1