用于查找一周中每天不同工作时间的空闲时段的存储过程 [英] Stored procedure to find free slots with different work times per day of the week

查看：25
本文介绍了用于查找一周中每天不同工作时间的空闲时段的存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用由 Aaron Bertrand 创建的很棒的存储过程 作为一个开始，因为它几乎可以满足我的需求.遗憾的是，我的 SQL 技能不如他，所以我需要一些帮助.我需要做的是找到受特定用户工作时间限制的开始日期和结束日期之间的空闲时间段.我的要求与 OP 有点不同:

I am using the awesome stored procedure created by Aaron Bertrand here as a start since it almost does what I need. Sadly my SQL skills are not as good as his so I need some help here. What I need to do is to find free time slots between a start and end date constrained by the time of work of a specific user. My requirements are a little different from OP:

  1. 我只需要 1 个用户的空闲插槽.
  2. 用户可以在一周中的每一天有不同的工作时间.
  3. 用户在一周中的某些天无法工作
  4. 用户可以休息一下，这是他不能做任何工作的时间
  5. 没有递归事件

所以，新的表结构将是这样的:

So, the new table structure would be something like this:

CREATE TABLE [dbo].[event]
(
    [event_id] [int] IDENTITY(1,1) NOT NULL,
    [event_start] [datetime] NULL,
    [event_end] [datetime] NULL,
    [UserId] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Users]
(
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [Username] [varchar](32) NULL,
    [Sunday] [bit] NULL,
    [Monday] [bit] NULL,
    [Tuesday] [bit] NULL,
    [Wednesday] [bit] NULL,
    [Thursday] [bit] NULL,
    [Friday] [bit] NULL,
    [Saturday] [bit] NULL,
    [WorkStartSunday] [time](7) NULL,
    [WorkEndSunday] [time](7) NULL,
    [IntervalStartSunday] [time](7) NULL,
    [IntervalEndSunday] [time](7) NULL,
    [WorkStartMonday] [time](7) NULL,
    [WorkEndMonday] [time](7) NULL,
    [IntervalStartMonday] [time](7) NULL,
    [IntervalEndMonday] [time](7) NULL,
    [WorkStartTuesday] [time](7) NULL,
    [WorkEndTuesday] [time](7) NULL,
    [IntervalStartTuesday] [time](7) NULL,
    [IntervalEndTuesday] [time](7) NULL,
    [WorkStartWednesday] [time](7) NULL,
    [WorkEndWednesday] [time](7) NULL,
    [IntervalStartWednesday] [time](7) NULL,
    [IntervalEndWednesday] [time](7) NULL,
    [WorkStartThursday] [time](7) NULL,
    [WorkEndThursday] [time](7) NULL,
    [IntervalStartThursday] [time](7) NULL,
    [IntervalEndThursday] [time](7) NULL,
    [WorkStartFriday] [time](7) NULL,
    [WorkEndFriday] [time](7) NULL,
    [IntervalStartFriday] [time](7) NULL,
    [IntervalEndFriday] [time](7) NULL,
    [WorkStartSaturday] [time](7) NULL,
    [WorkEndSaturday] [time](7) NULL,
    [IntervalStartSaturday] [time](7) NULL,
    [IntervalEndSaturday] [time](7) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

一些示例数据如下:

INSERT [dbo].[Users] ([UserID], [Username], [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [WorkStartSunday], [WorkEndSunday], [IntervalStartSunday], [IntervalEndSunday], [WorkStartMonday], [WorkEndMonday], [IntervalStartMonday], [IntervalEndMonday], [WorkStartTuesday], [WorkEndTuesday], [IntervalStartTuesday], [IntervalEndTuesday], [WorkStartWednesday], [WorkEndWednesday], [IntervalStartWednesday], [IntervalEndWednesday], [WorkStartThursday], [WorkEndThursday], [IntervalStartThursday], [IntervalEndThursday], [WorkStartFriday], [WorkEndFriday], [IntervalStartFriday], [IntervalEndFriday], [WorkStartSaturday], [WorkEndSaturday], [IntervalStartSaturday], [IntervalEndSaturday]) 
VALUES (1, N'User A', 0, 1, 1, 0, 0, 0, 0, NULL, NULL, NULL, NULL, CAST(N'08:00:00' AS Time), CAST(N'18:00:00' AS Time), CAST(N'12:00:00' AS Time), CAST(N'13:00:00' AS Time), CAST(N'10:00:00' AS Time), CAST(N'15:00:00' AS Time), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

我需要能够传递事件的持续时间、开始/结束日期和用户 ID.所有其他数据都来自用户表.我能够解决要求 3 和部分 2.2 是我最大的问题 - 我可以在正确的时间开始，但不知道如何才能在正确的时间结束.4 我什至还没试过 :/

I need to be able to pass the duration of the event, the start/end date and the UserId. All other data comes from the user table. I was able to resolve requirement 3 and partially 2. 2 is my biggest problem - I can start at the right time but do not know how I can make it to end at the right time. 4 I did not even try yet :/

警告:这是我第一次使用如此复杂的存储过程，所以我对它所做的事情并不漂亮(对不起，亚伦).我相信这会让 DBA 哭泣.这是我到现在为止得到的(它采用 SQL 的形式，因为它更容易测试):

Warning: This is the first time ever that I play with such a complex stored procedures so it is not beautiful what I done to it (sorry, Aaron). I am sure this will make DBAs cry. Here is what I got until now (it is in the form of a SQL because it is easier to test):

--parameters
declare @UserId int =  1
declare @Duration INT = 30;
declare @StartDate SMALLDATETIME = '12-22-2014';
declare @EndDate SMALLDATETIME = '12-25-2014';

--user configurations
declare @Sunday bit = 0;
declare @Monday bit = 0;
declare @Tuesday bit = 0;
declare @Wednesday bit = 0;
declare @Thursday bit = 0;
declare @Friday bit = 0;
declare @Saturday bit = 0;

declare @StartSunday time;
declare @EndSunday time;

declare @StartMonday time;
declare @EndMonday time;
declare @StartIntervalMonday time;
declare @EndIntervalMonday time;

declare @StartTuesday time;
declare @EndTuesday time;
declare @StartIntervalTuesday time;
declare @EndIntervalTuesday time;

declare @StartWednesday time;
declare @EndWednesday time;

declare @StartThursday time;
declare @EndThursday time;

declare @StartFriday time;
declare @EndFriday time;

declare @StartSaturday time;
declare @EndSaturday time;

--load user configurations
select 
   @Sunday = us.Sunday,
   @Monday = us.Monday,
   @Tuesday = us.Tuesday,
   @Wednesday = us.Wednesday,
   @Thursday = us.Thursday,
   @Friday = us.Friday,
   @Saturday = us.Saturday,
   @StartSunday = us.WorkStartMonday,
   @EndSunday = us.WorkEndSunday,
   @StartMonday = us.WorkStartMonday,
   @EndMonday = us.WorkEndMonday,
   @StartIntervalMonday = us.IntervalStartMonday,
   @EndIntervalMonday = us.IntervalEndMonday,
   @StartTuesday = us.WorkStartTuesday,
   @EndTuesday = us.WorkEndTuesday,
   @StartIntervalTuesday = us.IntervalStartTuesday,
   @EndIntervalTuesday = us.IntervalEndTuesday,
   @StartWednesday = us.WorkStartWednesday,
   @EndWednesday = us.WorkEndWednesday,
   @StartThursday = us.WorkStartThursday,
   @EndThursday = us.WorkEndThursday,
   @StartFriday = us.WorkStartFriday,
   @EndFriday = us.WorkEndThursday,
   @StartSaturday = us.WorkStartSaturday,
   @EndSaturday = us.WorkEndThursday
from 
   Users us
where 
   us.UserId = @UserID

;WITH dRange(d) AS
(

    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 

    where DATEADD(DAY, n-1, @StartDate) >= @StartDate
    and DATEADD(DAY, n-1, @StartDate) <= @EndDate

    and DATEPART(WEEKDAY,DATEADD(DAY, n-1, @StartDate)) in 
    (
        IIF(@Sunday =1,1,null),
        IIF(@Monday =1,2,null),
        IIF(@Tuesday =1,3,null),
        IIF(@Wednesday =1,4,null),
        IIF(@Thursday =1,5,null),
        IIF(@Friday =1,6,null),
        IIF(@Saturday=1,7,null)
        )

    ), possible(ds, de) AS
(
    --with this horrendous thing I set the start time for each weekday
    SELECT          
    CASE 
    WHEN DATEPART(WEEKDAY,dRange.d) = 1 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartSunday), dRange.d)) 
    WHEN DATEPART(WEEKDAY,dRange.d) = 2 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartMonday), dRange.d)) 
    WHEN DATEPART(WEEKDAY,dRange.d) = 3 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartTuesday), dRange.d)) 
    WHEN DATEPART(WEEKDAY,dRange.d) = 4 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartWednesday), dRange.d)) 
    WHEN DATEPART(WEEKDAY,dRange.d) = 5 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartThursday), dRange.d)) 
    WHEN DATEPART(WEEKDAY,dRange.d) = 6 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartFriday), dRange.d)) 
    WHEN DATEPART(WEEKDAY,dRange.d) = 7 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartSaturday), dRange.d)) 
    END,

    CASE 
    WHEN DATEPART(WEEKDAY,dRange.d) = 1 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartSunday), dRange.d)) 
    WHEN DATEPART(WEEKDAY,dRange.d) = 2 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartMonday), dRange.d)) 
    WHEN DATEPART(WEEKDAY,dRange.d) = 3 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartTuesday), dRange.d)) 
    WHEN DATEPART(WEEKDAY,dRange.d) = 4 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartWednesday), dRange.d)) 
    WHEN DATEPART(WEEKDAY,dRange.d) = 5 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartThursday), dRange.d)) 
    WHEN DATEPART(WEEKDAY,dRange.d) = 6 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartFriday), dRange.d)) 
    WHEN DATEPART(WEEKDAY,dRange.d) = 7 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartSaturday), dRange.d)) 
    END

    FROM        
    --but here is the problem - if I understood it correctly, this select limits the day events but I was not able to do a CASE here since I do not know how to access the date
    --@StartMonday/@EndMonday should change to reflect the specific day of the week

    (SELECT TOP ((DATEDIFF(HOUR,@StartMonday,@EndMonday)*60)/@Duration) 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 not exists
(
    select 1 from 
    (
        select UserId,event_start,event_end
        from event

        where event_start >= @StartDate
        and event_start < DATEADD(DAY, 1, @EndDate)
        ) as sub

    WHERE sub.UserId IN 
    (
        SELECT UserId FROM Users AS m
        WHERE UserId = @UserId
        AND (p.de > sub.event_start AND p.ds < sub.event_end)
        )        
    )
order by ds

很明显，不同的工作日(不同的星号/结束)应该返回不同的行数，但我不知道如何在 select top 上做到这一点.在这一点上，我仍然不知道如何解决 Interval 问题，但也许这是另一个问题，或者我可以在客户端忽略它们.帮助?

It is obvious that different weekdays (with different star/end) should return a different number of rows but I do not know how to do it on the select top. At this point I still do not know how will I resolve the Interval problem but maybe this is another question, or maybe I can ignore them at client side. Help?

编辑 1

问题在于 TOP 子句应限制为特定工作日的小时数.

THe problem is that the TOP clause should limit to the number of hours of the specific week day.

如果我通过开始/结束日期 12-22-2014 并输入 (SELECT TOP ((DATEDIFF(HOUR,@StartMonday,@EndMonday)*60)/@Duration)，它完美地工作.如果我通过开始/结束日期 12-23-2014 并输入 (SELECT TOP ((DATEDIFF(HOUR,@StartTuesday,@EndTuesday)*60)/@Duration)，它可以完美运行.

If I pass to start/end the date 12-22-2014 and put (SELECT TOP ((DATEDIFF(HOUR,@StartMonday,@EndMonday)*60)/@Duration), it works perfectly. If I pass to start/end the date 12-23-2014 and put (SELECT TOP ((DATEDIFF(HOUR,@StartTuesday,@EndTuesday)*60)/@Duration), it works perfectly.

但是如果我通过 start 12-22-2014 和 end 12-23-2014，它不起作用，因为我不知道在那里做一个 CASE.结果如下:

But if I pass start 12-22-2014 and end 12-23-2014, it does not work because I do not know hot to do a CASE there. Here is the results:

配置说:

WorkStartMonday WorkEndMonday   WorkStartTuesday    WorkEndTuesday
08:00           18:00           10:00               15:00

在同一天开始/结束(22/12/2014，持续时间 = 60)，结果是:

With start/end at the same day (22/12/2014, Duration = 60) the result is:

2014-12-22 08:00:00 - 2014-12-22 09:00:00
2014-12-22 09:00:00 - 2014-12-22 10:00:00
2014-12-22 10:00:00 - 2014-12-22 11:00:00
2014-12-22 11:00:00 - 2014-12-22 12:00:00
2014-12-22 12:00:00 - 2014-12-22 13:00:00
2014-12-22 13:00:00 - 2014-12-22 14:00:00
2014-12-22 14:00:00 - 2014-12-22 15:00:00
2014-12-22 15:00:00 - 2014-12-22 16:00:00
2014-12-22 16:00:00 - 2014-12-22 17:00:00
2014-12-22 17:00:00 - 2014-12-22 18:00:00

开始/结束于 23/12/2014，结果也正确:

With start/end on 23/12/2014, the result is also correct:

2014-12-23 10:00:00 2014-12-23 11:00:00
2014-12-23 11:00:00 2014-12-23 12:00:00
2014-12-23 12:00:00 2014-12-23 13:00:00
2014-12-23 13:00:00 2014-12-23 14:00:00
2014-12-23 14:00:00 2014-12-23 15:00:00

发生这种情况是因为 TOP 子句限制为每个工作日的总小时数.如果我有超过一天的时间，我应该在那个 TOP 条件下做一个案例，但我如何访问日期"?

This happens because the TOP clause limits to the total hours of each weekday. If I have more than one day I should do a case on that TOP condition but how can I access the "date"?

推荐答案

好的，这个答案建立在上一个答案的基础上，我们将添加午餐时间间隔和事件，并查看事件的日期范围.

Ok, this answer builds on the last one, we're going to add the lunch intervals and the events and going to look at a date range for events.

首先让我们制作一些测试数据:

First lets make some test data:

DECLARE @event TABLE
(
    [event_id] [int] IDENTITY(1,1),
    [event_start] [datetime],
    [event_end] [datetime],
    [UserId] [int]
)

INSERT INTO @event
( event_start, event_end, UserId )
VALUES
('12/01/2014 09:00:00', '12/01/2014 09:30:00', 1),
('12/01/2014 09:30:00', '12/01/2014 10:30:00', 1),
('12/01/2014 11:00:00', '12/01/2014 12:00:00', 1),
('12/01/2014 13:00:00', '12/01/2014 16:30:00', 1);

DECLARE @Users TABLE
(
    [UserID] [int],
    [Username] [varchar](32) NULL,
    [Sunday] [bit] NULL,
    [Monday] [bit] NULL,
    [Tuesday] [bit] NULL,
    [Wednesday] [bit] NULL,
    [Thursday] [bit] NULL,
    [Friday] [bit] NULL,
    [Saturday] [bit] NULL,
    [WorkStartSunday] [time](7) NULL,
    [WorkEndSunday] [time](7) NULL,
    [IntervalStartSunday] [time](7) NULL,
    [IntervalEndSunday] [time](7) NULL,
    [WorkStartMonday] [time](7) NULL,
    [WorkEndMonday] [time](7) NULL,
    [IntervalStartMonday] [time](7) NULL,
    [IntervalEndMonday] [time](7) NULL,
    [WorkStartTuesday] [time](7) NULL,
    [WorkEndTuesday] [time](7) NULL,
    [IntervalStartTuesday] [time](7) NULL,
    [IntervalEndTuesday] [time](7) NULL,
    [WorkStartWednesday] [time](7) NULL,
    [WorkEndWednesday] [time](7) NULL,
    [IntervalStartWednesday] [time](7) NULL,
    [IntervalEndWednesday] [time](7) NULL,
    [WorkStartThursday] [time](7) NULL,
    [WorkEndThursday] [time](7) NULL,
    [IntervalStartThursday] [time](7) NULL,
    [IntervalEndThursday] [time](7) NULL,
    [WorkStartFriday] [time](7) NULL,
    [WorkEndFriday] [time](7) NULL,
    [IntervalStartFriday] [time](7) NULL,
    [IntervalEndFriday] [time](7) NULL,
    [WorkStartSaturday] [time](7) NULL,
    [WorkEndSaturday] [time](7) NULL,
    [IntervalStartSaturday] [time](7) NULL,
    [IntervalEndSaturday] [time](7) NULL
);

INSERT INTO @Users 
([UserID], [Username], [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [WorkStartSunday], [WorkEndSunday], [IntervalStartSunday], [IntervalEndSunday], [WorkStartMonday], [WorkEndMonday], [IntervalStartMonday], [IntervalEndMonday], [WorkStartTuesday], [WorkEndTuesday], [IntervalStartTuesday], [IntervalEndTuesday], [WorkStartWednesday], [WorkEndWednesday], [IntervalStartWednesday], [IntervalEndWednesday], [WorkStartThursday], [WorkEndThursday], [IntervalStartThursday], [IntervalEndThursday], [WorkStartFriday], [WorkEndFriday], [IntervalStartFriday], [IntervalEndFriday], [WorkStartSaturday], [WorkEndSaturday], [IntervalStartSaturday], [IntervalEndSaturday]) 
VALUES 
(1, 'User A', 0, 1, 1, 0, 0, 0, 0, NULL, NULL, NULL, NULL, CAST('08:00:00' AS Time), CAST('18:00:00' AS Time), CAST('12:00:00' AS Time), CAST('13:00:00' AS Time), CAST('10:00:00' AS Time), CAST('15:00:00' AS Time), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

再一次，我将创建一个数字表，现在让我们设置我们的数据范围，我还将根据 30 分钟的持续时间生成时间段.

Once again, I am going to create a numbers table, now lets set our data range, and I am also going to generate time slots based on a 30 minute duration.

DECLARE @yearstart DATETIME = '01/01/2014'
DECLARE @duration_minutes INT = 30
DECLARE @number_of_numbers INT = 100000;
;WITH
    a AS (SELECT 1 AS i UNION ALL SELECT 1),
    b AS (SELECT 1 AS i FROM a AS x, a AS y),
    c AS (SELECT 1 AS i FROM b AS x, b AS y),
    d AS (SELECT 1 AS i FROM c AS x, c AS y),
    e AS (SELECT 1 AS i FROM d AS x, d AS y),
    f AS (SELECT 1 AS i FROM e AS x, e AS y),
    numbers AS 
(
    SELECT TOP(@number_of_numbers)
    (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) - 1 AS number
    FROM f
)
, mydays AS
(
    SELECT DATEADD(DAY, number, @yearstart) CheckDate
    FROM numbers
)
, myduration AS
(
    SELECT DATEADD(MINUTE, number * @duration_minutes, @yearstart) CheckMinute
    FROM numbers
)
, workdays AS
(
    SELECT 
        m.CheckDate + CAST(u.WorkStartSunday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndSunday as datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 1 AND u.Sunday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartMonday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndMonday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 2 AND u.Monday = 1)
        UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartTuesday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndTuesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 3 AND u.Tuesday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartWednesday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndWednesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 4 AND u.Wednesday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartThursday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndThursday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 5 AND u.Thursday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartFriday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndFriday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 6 AND u.Friday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartSaturday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndSaturday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 7 AND u.Saturday = 1)
),
intervals AS
(
    SELECT 
        m.CheckDate + CAST(u.IntervalStartSunday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.IntervalEndSunday as datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 1 AND u.Sunday = 1)
        AND u.IntervalStartSunday IS NOT NULL
    UNION
    SELECT 
        m.CheckDate + CAST(u.IntervalStartMonday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.IntervalEndMonday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 2 AND u.Monday = 1)
        AND u.IntervalStartMonday IS NOT NULL
    UNION
    SELECT 
        m.CheckDate + CAST(u.IntervalStartTuesday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.IntervalEndTuesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 3 AND u.Tuesday = 1)
        AND u.IntervalStartTuesday IS NOT NULL
    UNION
    SELECT 
        m.CheckDate + CAST(u.IntervalStartWednesday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.IntervalEndWednesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 4 AND u.Wednesday = 1)
        AND u.IntervalStartWednesday IS NOT NULL
    UNION
    SELECT 
        m.CheckDate + CAST(u.IntervalStartThursday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.IntervalEndThursday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 5 AND u.Thursday = 1)
        AND u.IntervalStartThursday IS NOT NULL
    UNION
    SELECT 
        m.CheckDate + CAST(u.IntervalStartFriday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.IntervalEndFriday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 6 AND u.Friday = 1)
        AND u.IntervalStartFriday IS NOT NULL
    UNION
    SELECT 
        m.CheckDate + CAST(u.IntervalStartSaturday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.IntervalEndSaturday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 7 AND u.Saturday = 1)
        AND u.IntervalStartSaturday IS NOT NULL
)
, timeslots AS
(
    SELECT m.CheckMinute AS StartSlot,
        LEAD(m.CheckMinute) OVER (ORDER BY m.CheckMinute) AS EndSlot
    FROM myduration m
)

现在我还创建了时间间隔(午餐时间数据)，我们的最终输出将是一个时间段列表，以及它是开放的、事件的还是某个日期范围的午餐:

Now I also created the intervals (lunch hour data) and our final output will be a list of time slots and whether it is open, event, or lunch for a date range: 

select 
    t.StartSlot,
    t.EndSlot,
    CASE WHEN i.WorkStart IS NULL AND e.event_start IS NULL THEN 'Open' 
        WHEN i.WorkStart IS NOT NULL THEN 'Lunch'
        ELSE 'Event'
    END AS IType
from timeslots t
INNER JOIN workdays w
    ON t.StartSlot >= w.WorkStart 
    AND t.StartSlot < w.WorkEnd
LEFT JOIN intervals i
    ON t.StartSlot >= i.WorkStart
    AND t.StartSlot < i.WorkEnd
LEFT JOIN @event e
    ON t.StartSlot >= e.event_start
    AND t.StartSlot < e.event_end
WHERE w.WorkStart >= '12/01/2014'
    AND w.WorkEnd < '12/04/2014'
ORDER BY t.StartSlot

输出如下:

StartSlot               EndSlot                 IType
2014-12-01 08:00:00.000 2014-12-01 08:30:00.000 Open
2014-12-01 08:30:00.000 2014-12-01 09:00:00.000 Open
2014-12-01 09:00:00.000 2014-12-01 09:30:00.000 Event
2014-12-01 09:30:00.000 2014-12-01 10:00:00.000 Event
2014-12-01 10:00:00.000 2014-12-01 10:30:00.000 Event
2014-12-01 10:30:00.000 2014-12-01 11:00:00.000 Open
2014-12-01 11:00:00.000 2014-12-01 11:30:00.000 Event
2014-12-01 11:30:00.000 2014-12-01 12:00:00.000 Event
2014-12-01 12:00:00.000 2014-12-01 12:30:00.000 Lunch
2014-12-01 12:30:00.000 2014-12-01 13:00:00.000 Lunch
2014-12-01 13:00:00.000 2014-12-01 13:30:00.000 Event
2014-12-01 13:30:00.000 2014-12-01 14:00:00.000 Event
2014-12-01 14:00:00.000 2014-12-01 14:30:00.000 Event
2014-12-01 14:30:00.000 2014-12-01 15:00:00.000 Event
2014-12-01 15:00:00.000 2014-12-01 15:30:00.000 Event
2014-12-01 15:30:00.000 2014-12-01 16:00:00.000 Event
2014-12-01 16:00:00.000 2014-12-01 16:30:00.000 Event
2014-12-01 16:30:00.000 2014-12-01 17:00:00.000 Open
2014-12-01 17:00:00.000 2014-12-01 17:30:00.000 Open
2014-12-01 17:30:00.000 2014-12-01 18:00:00.000 Open
2014-12-02 10:00:00.000 2014-12-02 10:30:00.000 Open
2014-12-02 10:30:00.000 2014-12-02 11:00:00.000 Open
2014-12-02 11:00:00.000 2014-12-02 11:30:00.000 Open
2014-12-02 11:30:00.000 2014-12-02 12:00:00.000 Open
2014-12-02 12:00:00.000 2014-12-02 12:30:00.000 Open
2014-12-02 12:30:00.000 2014-12-02 13:00:00.000 Open
2014-12-02 13:00:00.000 2014-12-02 13:30:00.000 Open
2014-12-02 13:30:00.000 2014-12-02 14:00:00.000 Open
2014-12-02 14:00:00.000 2014-12-02 14:30:00.000 Open
2014-12-02 14:30:00.000 2014-12-02 15:00:00.000 Open

这篇关于用于查找一周中每天不同工作时间的空闲时段的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆