Сравнение структур DateTime для поиска свободных слотов

Я хотел бы просмотреть события всех пользователей в списке и получить все время, когда каждый пользователь свободен от 30 минут или больше между 7 AM-7PM.

Однако существует признак, если метод помечен как "повторяющийся", то есть для повторения бит установлено значение 1, то это событие повторяется в течение 52 недель после его начала (поэтому время недоступно). Получение этих событий осуществляется в хранимой процедуре.

Мой код до сих пор ниже. Я собираюсь написать эту процедуру правильно? Я не уверен, как приступить к возврату функции, как хотелось бы. Кто-нибудь сможет мне помочь?

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
  }

Структура таблицы:

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

Пример функциональности, которую я бы хотел:

Пользователь добавляет несколько пользователей из базы данных в список. Пользователь выбирает период времени, в течение которого он хотел бы провести встречу со всеми этими пользователями. Мой алгоритм вычисляет все периоды времени, которые являются бесплатными для всех пользователей (т.е. Время, подходящее для встречи между всеми пользователями и > 30 минут).

Дополнительная информация:

Примеры случаев:

  • Пользователь A пытается организовать встречу с пользователем B. Все временные интервалы свободно. Я бы хотел, чтобы алгоритм возвращал начало DateTime и DateTime конец всех возможных комбинаций времени начала и конца времена, составляющие > 30 минут и == продолжительность (параметр).

  • Типичный случай: пользователь A имеет события, запланированные на все времена, кроме 18:00 - 7 вечера. Он пытается организовать встречу с пользователем B на время 1 час. Пользователь B не организовал никаких мероприятий - DateTime 6PM и DateTime 7pm возвращаются, чтобы указать время начала и окончания встречи.

  • Повторяющийся случай: пользователь A имеет повторяющееся событие в 17.00-18.00 в понедельник. Он пытается организовать встречу по 2 часа в понедельник через шесть недель. Все будут возвращены комбинации начала DateTime и даты DateTime с разницей в 2 часа. Время от 17:00 до 19:00 не возвращается, поскольку это событие повторяется и происходит каждую неделю в течение 52 недель.

Вот хранимая процедура, которая извлекает все пользовательские события за заданный период времени (начало, конец):

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

Ответы

Ответ 1

Итак, представьте несколько таблиц:

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

И представьте, что некоторые примеры данных не так сложно обеспечить:

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

Теперь мы можем создать эту хранимую процедуру:

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

Примеры вызовов:

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

Результаты:

no events for either user

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

Результаты:

user A is busy almost all day

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

Результаты:

user A has a recurring meeting

Теперь обратите внимание, что я позаботился о нескольких факторах, которые вы либо не рассмотрели, либо не упомянули (например, повторяющееся событие, которое начинается в будущем). С другой стороны, я также не имел дело с некоторыми другими факторами (например, летнее время, если это вообще может повлиять на это), и не тестировало все возможные сценарии (например, несколько событий в тот же день, который уже существует).

Я тестировал, что если вы пройдете диапазон (например, 2012-03-12 → 2012-03-14), вы по существу просто получите объединение вышеуказанных результатов с примерно одинаковыми временными интервалами (они будут меняться исходя из продолжительности курса). Важная часть состоит в том, что временные интервалы затемнения соблюдаются. Я не тестировал логику для случая, когда повторное событие начинается в будущем, а предоставленный диапазон дат включает этот будний день как до, так и после первого экземпляра события.

Если какой-либо случай не работает для вас, то именно поэтому важно, чтобы вы показывали нам все ваши дела, используя пример данных, а не проблемы с текстом, а также объясняли желаемые результаты запроса, учитывая данные.

EDIT - для обработки более двух пользователей вам потребуется всего несколько изменений. Если вы добавите функцию разделения следующим образом:

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

Теперь очень незначительные изменения в хранимой процедуре (я забыл неизменные биты):

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

Итак, ваш звонок просто немного меняется:

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

Просто убедитесь, что запросчик включен в список, разделенный запятыми.

PS это добавление не проверено.