比较的DateTime结构找到空闲插槽 [英] Comparing DateTime structs to find free slots

查看:320
本文介绍了比较的DateTime结构找到空闲插槽的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想通过所有用户在列表中的事件搜索和检索所有的时间,每一个用户是免费的30分钟或7 AM-7PM之间的更大。

有,是一个catch但是如果一个方法被标记为重复,即该位的重复被置为1,则该事件复发的一段52个星期的开始后(所以时间不可用)。这些事件的取出是在存储过程中的照顾。

我的code到目前为止如下。我要对写这个程序的正确方法?我真的不知道如何着手,以获得函数返回的,我想。会有人能够帮助我?

 名单,其中,串>用户名= //用户名列表中。
日期时间开始= //日期时间,你会为周期开始要安排会议
日期时间结束= //日期时间为期末
// INT分钟= //会议时间(必须是30分钟以上)

的foreach(在用户名字符串的用户名){
   //获取该用户的事件
    变种DB = Database.Open(mPlan);
    名单< D​​ateTime的> startTimes;
    名单<日期时间endTimes;
    //此存储过程返回用户的所有事件在给定的时间段,
    //包括经常性的事件。
    VAR纪录= db.Query(EXEC dbo.GetEvents @ 0,@ 1,@ 2,用户名,开始,结束);
    的foreach(在结果VAR记录){
          startTimes.Add(record.event_start);
          endTimes.Add(record.event_end);
    }
    //所以现在我把所有的清单开始时间和结束时间的事件
    //为一个用户,并可以保存所有这些数据在列表
  }
 

表结构:

定义@users表 (     用户ID INT IDENTITY(1,1),     名VARCHAR(32) ); DECLARE @Groups表 (     组ID INT IDENTITY(1,1),     组名VARCHAR(32) ); DECLARE @Membership表 (     用户名INT,     组ID INT ); DECLARE @event表 (     EVENT_ID INT IDENTITY(1,1),     event_start DATETIME,     event_end DATETIME,     GROUP_ID INT,     反复出现的位 );

的功能,例如,我想:

用户从数据库列表添加多个用户。用户选择的时间段,他想与所有这些用户的会议。我的算法计算所有时间段是免费为所有用户(即时间,这将是适合让所有用户之间的会议,是> 30分钟)。

其他信息:

样的情况:

  • 用户A试图组织与用户B会议所有时隙 自由。我想算法返回日期时间的开始和 的开始时间和结束所有可能的组合日期时间结束 时间是> 30分钟和==持续时间(参数)。

  • 典型案例:用户A计划为所有时间下午6点以外的事件 - 晚上7点。他试图组织与用户B会议的持续时间 1小时。用户B没有组织的活动 - 日期时间下午6点 日期时间下午7时被返回,以指示的开始和结束时间 会议。

  • 复发的情况:用户A在5时至6时在星期一重复发生的事件。 他试图组织2小时的会议,在六个星期的时间是星期一。所有 日期时间开始和日期时间结束的组合,其中有2小时的差被返回。时间5 pm-7pm是 没有回来,因为此事件是经常性和发生 每周为52周。

下面是存储过程以检索所有的用户事件设定的时间段(开始,结束):

ALTER PROCEDURE dbo.GetEvents   @UserName VARCHAR(50),   @StartDate DATETIME,   @EndDate DATETIME 如 开始 - 定义一个CTE来获取所有团体有关联与当前用户 与基团; (SELECT组ID     会籍米             INNER JOIN用户ü                 ON m.UserID = u.UserID     其中username = @UserName     GROUP BY组ID ), - 定义一个CTE来获取所有事件上述定义的组 AllEvents AS (选择e。*     从事件e             INNER JOIN组米                 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     从事件e             INNER JOIN组米                 ON m.GroupID = e.group_id             CROSS JOIN             (SELECT ROW_NUMBER()OVER(ORDER BY OBJECT_ID)AS周                 从sys.objects中             )为W     WHERE e.recurring = 1 ) - 获取所有比赛时,事件超出所定义的时期 选择 * 从AllEvents WHERE Event_Start> = @StartDate 与Event_End< = @EndDate 结束

解决方案

所以,想象一些表:

使用tempdb; 走 CREATE TABLE dbo.Users (     用户ID INT IDENTITY(1,1),     名VARCHAR(32) ); CREATE TABLE dbo.Groups (     组ID INT IDENTITY(1,1),     组名VARCHAR(32) ); CREATE TABLE dbo.Membership (     用户名INT,     组ID INT ); CREATE TABLE DBO。[事件] (     EVENT_ID INT IDENTITY(1,1),     event_start DATETIME,     event_end DATETIME,     GROUP_ID INT,     反复出现的位 );

和想象,一些示例数据并没有那么难提供:

插入dbo.Users(用户名)     选择用户A'UNION ALL SELECT用户B; INSERT dbo.Groups(组名)     选择组1UNION ALL SELECT组2; INSERT dbo.Membership(用户名,组ID)     SELECT 1,1 UNION ALL SELECT 2,2; INSERT DBO。[事件](event_start,event_end,GROUP_ID,经常性) - 用户A,几乎一整天会议的具体日期 选择20120313 07:00','20120313 18:00,1,0 - 用户A,定期会议每星期一 UNION ALL SELECT20120312 17:00','20120312 18:00,1,1 - 用户A,定期召开的会议每周二(未来) UNION ALL SELECT20120327 14:00','20120327 15:00',1,1; 走

现在我们可以建立这个存储过程:

CREATE PROCEDURE dbo.GetPossibleMeetingTimes     @AskingUserID INT,     @TargetUserID INT,     @Duration INT, - 在几分钟内!     @StartDate SMALLDATETIME, - 假定日期,没有时间!     @EndDate SMALLDATETIME - 再 - 迄今为止,还没有时间! 如 开始     SET NOCOUNT ON;     ;具有dRange的(d)     (          - 得到所要求的范围内的实际日期          - 限制中的行数sys.objects中         SELECT TOP(DATEDIFF(DAY,@StartDate,@EndDate)+1)             DATEADD(天,n-1,@StartDate)          FROM(SELECT N = ROW_NUMBER()OVER(ORDER BY [OBJECT_ID])           从sys.objects中)为X     ),可能的(顺,反)的AS     (          - 获得@Duration分钟的所有时隙          - 上午7:00和下午7:00的每一天之间          - 范围 - 这些都是潜在的* *插槽         SELECT DATEADD(MINUTE,30 * RN,DATEADD(小时,​​7,dRange.d)),             DATEADD(MINUTE,30 * RN + @Duration,DATEADD(小时,​​7,dRange.d))         FROM(SELECT TOP(三十〇分之七百二十零)RN = ROW_NUMBER()OVER         (ORDER BY [OBJECT_ID]) - 1 sys.objects中)为X         CROSS JOIN dRange     )     SELECT p.ds,p.de从可能为P     WHERE p.de< = DATEADD(小时,​​19,DATEADD(DAY,DATEDIFF(DAY,0,p.de),0))     和NOT EXISTS     (         SELECT 1 FROM         (              - 渗透到用户范围内的日子里事件             SELECT GROUP_ID,event_start,event_end                 从DBO。[事件]                 WHERE event_start> = @StartDate                 与event_start< DATEADD(天,1,@EndDate)             UNION ALL              - 还包括用户在同一平日经常性事件(S)              - 归到匹配天的范围内             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)             从DBO。[事件]             如果重复= 1             与event_start< = DATEADD(天,1,@EndDate) - 忽略未来事件                     与event_start> = DATEADD(周,-52,@EndDate) - 53周后             和DATEDIFF(DAY,event_start,p.ds)%7 = 0 - 同一工作日         )AS子         WHERE sub.group_id IN         (              - 这种检查的事件都在previously预定的时间             选择组ID FROM dbo.Membership               其中userid IN(@AskingUserID,@TargetUserID)               AND(p.de> sub.event_start和p.ds< sub.event_end)         )     )     ORDER BY p.ds,p.de; 结束 走

举例称:

- 案例1:用户A试图与用户B,以满足一天的地方 - 无论时间表是明确的。 EXEC dbo.GetPossibleMeetingTimes     @AskingUserID = 1,     @TargetUserID = 2,     @duration = 30,     @StartDate ='20120314', - 没有事件或者用户     @EndDate ='20120314';

结果:

- 案例2:用户A试图与用户B,以满足一个小时,上 - 每天在这里用户A已经从早上7点开会到下午6点。 EXEC dbo.GetPossibleMeetingTimes     @AskingUserID = 1,     @TargetUserID = 2,     @Duration = 60,     @StartDate ='20120313', - 用户A具有几乎全天事件     @EndDate ='20120313';

结果:

- 案例3:用户A试图与用户B,以满足两个小时,在 - 一个工作日,其中用户A的定期会议从下午5-6点 EXEC dbo.GetPossibleMeetingTimes     @AskingUserID = 1,     @TargetUserID = 2,     @Duration = 120,     @StartDate ='20120319', - 用户A的定期会议     @EndDate ='20120319';

结果:

现在请注意,我把你要么还没有考虑或没有提到(例如,在未来开始重复发生的事件)几个因素的照顾。在另一方面,我也没有处理一些其他因素(如夏令时,如果它可能会影响这在所有),并没有测试所有可能出现的情况(在已存在的同一天,例如多个事件)。

我做了测试,如果你传递了一个范围(例如2012-03-12 - > 2012-03-14),你会基本上是刚刚获得的上述结果的联合与大致可用相同的时隙(这些变化基于当然持续时间)。最重要的是,停电时隙感到非常荣幸。的情况是重复发生的事件开始,并在未来提供的日期范围包括平日之前和事件的第一个实例后,我没有测试的逻辑。

如果任何情况下不适合你的工作,那么这就是为什么,你告诉我们所有的情况下的使用样本数据,而不是字的问题是很重要的,也解释查询的预期效果鉴于数据

修改 - 要处理超过2的用户,你只需要一些变化。如果添加了拆分功能如下:

CREATE FUNCTION dbo.SplitInts(@list VARCHAR(MAX)) 返回值表 如    返回    (选择项目=转换(INT,项目)FROM(       选择项目= xivalue('(./文())[1]','诚信')FROM(        选择[XML] =转换(XML,'< I>+ REPLACE(@list,',','< / I>< I>')          +'< / I>'。'。')查询())作为跨APPLY [XML] .nodes(I)为X(I))为y        其中item IS NOT NULL    );

现在非常微小的变化对存储过程(我已经离开了不变位):

ALTER PROCEDURE dbo.GetPossibleMeetingTimes     @UserIDList VARCHAR(MAX), - 删除另外两个参数     @Duration INT,     @StartDate SMALLDATETIME,     @EndDate SMALLDATETIME 如 ...         WHERE sub.group_id IN - 改变了这种子查询中的code         (             选择组ID FROM dbo.Membership为M               INNER JOIN dbo.SplitInts(@UserIDList)正如我               ON m.UserID = i.Item               WHERE(p.de> sub.event_start和p.ds< sub.event_end)         ) ...

于是你的电话稍稍改变为:

EXEC dbo.GetPossibleMeetingTimes      @UserIDList ='1,2,3,4,5',      @duration = 30,      @StartDate ='20120314',      @EndDate ='20120314';

只要确保请求者包含在逗号分隔的列表。

PS本增编是未经检验的。

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.

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.

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?

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
  }

Table structure:

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
);

Example of functionality I would like :

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 ).

Additional information :

Sample cases :

  • 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 ).

  • 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.

  • 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.

Here is the stored procedure which retrieves all of a users events for a set time period (start, end):

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 

解决方案

So imagine some tables:

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
);

And imagine that some sample data weren't so difficult to provide:

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

Now we can build this stored procedure:

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 <= 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 >= @StartDate 
                AND event_start < 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 <= DATEADD(DAY, 1, @EndDate) -- ignore future events
                    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 < sub.event_end)
        )
    )
    ORDER BY p.ds, p.de;
END
GO

Example calls:

-- 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';

Results:

-- 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';

Results:

-- 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';

Results:

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).

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.

If any case doesn't work for you, then this is exactly why it's important that you show us all your cases using sample data, not word problems and also explain the desired results of the query given that data.

EDIT - to handle more than 2 users, you only need a few changes. If you add a split function as follows:

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, '<i>' + REPLACE(@List, ',', '</i><i>') 
         + '</i>').query('.')) AS a CROSS APPLY [XML].nodes('i') AS x(i)) AS y
       WHERE Item IS NOT NULL
   );

Now very minor changes to the stored procedure (I've left out the unchanged bits):

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 < sub.event_end)
        )
...

So then your call just changes slightly to:

EXEC dbo.GetPossibleMeetingTimes
     @UserIDList = '1,2,3,4,5',
     @Duration   = 30,
     @StartDate  = '20120314',
     @EndDate    = '20120314';

Just make sure that the requester is included in the comma-separated list.

PS this addendum is untested.

这篇关于比较的DateTime结构找到空闲插槽的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆