sql-server - 在夜类之间的超时记录中拆分时间

Question

我是 SQL Server 的新手，我正在尝试从员工轮类表中拆分夜类之间的超时记录，但我很难做到这一点。夜类从晚上 10 点到早上 7 点

下面的示例可能有助于使事情更清楚。

例如

EmployeeId   InTime                     OutTime                  
----------   -----------------------    ----------------------- 
1            2018-08-10 21:00:00.000   2018-08-11 09:00:00.000  

期望的输出

EmployeeId   InTime                     OutTime                   Type
----------  -----------------------    -----------------------   -------
1            2018-08-10 21:00:00.000    2018-08-10 22:00:00.000   day
1            2018-08-10 22:00:00.000    2018-08-11 07:00:00.000   night
1            2018-08-11 07:00:00.000    2018-08-11 09:00:00.000   day

当前表的代码:

DECLARE @tbl TABLE 
    (
        EmployeeId INT,
        InTime DATETIME,
        OutTime DATETIME
    )

INSERT INTO @tbl (Employee_Id, Shift_Start, Shift_end) 
VALUES (1, '2018-08-10 21:00:00.000', '2018-08-11 09:00:00.000')

Answer 1

生成所有类次的列表并将其加入员工工时列表。请注意，这不限于小时或分钟。您甚至可以保留毫秒数。

create table EmpHours ( EmployeeID int, InTime datetime, OutTime datetime )

insert into EmpHours ( EmployeeID, InTime, OutTime ) values
( 1, '2018-08-10T21:00:00.000', '2018-08-11T09:00:00.000' ),
( 2, '2000-08-28T17:18:19.123', '2000-08-29T11:12:13.456' ),
( 2, '2000-12-31T16:00:00.555', '2001-01-01T12:44:55.444' ),
( 3, '2018-08-01T18:00:01.123', '2018-08-04T09:09:09.009' ),
( 4, '2018-08-10T09:00:00.000', '2018-08-10T17:00:00.000' ),
( 4, '2018-08-12T23:00:00.023', '2018-08-13T03:04:05.000' ),
( 5, '2018-08-10T17:00:00.017', '2018-08-10T23:08:08.008' ),
( 6, '2018-08-10T03:00:00.703', '2018-08-10T04:04:04.704' )

;
with 
-- Determine the earliest and latest dates.
-- Cast to date to remove the time portion.
-- Cast results back to datetime because we're going to add hours later.
MinMaxDates 
as 
(select cast(min(cast(InTime  as date))as datetime) as MinDate, 
        cast(max(cast(OutTime as date))as datetime) as MaxDate from EmpHours),

-- How many days have passed during that period
Dur
as
(select datediff(day,MinDate,MaxDate) as Duration from MinMaxDates),

-- Create a list of numbers.
-- These will be added to MinDate to get a list of dates.
-- We add a margin to deal with edge cases
NumList
as
( select -2 as Num
  union all
  select Num+1 from NumList,Dur where Num<=Duration+2 ),

-- Create a list of dates by adding those numbers to MinDate
DayList 
as
( select dateadd(day,Num,MinDate)as WorkDate from NumList, MinMaxDates  ),

-- Create a list of shifts starting on those dates
ShiftList
as
( select dateadd(hour, 7,WorkDate) as StartTime, -- from 7 AM to 10 PM
         dateadd(hour,22,WorkDate) as EndTime,
         'day' as [Type]
         from DayList
  union
  select dateadd(hour,22,WorkDate) as StartTime, -- from 10 PM to 7 AM of the next day
         dateadd(hour,31,WorkDate) as EndTime,
         'night' as [Type]
         from DayList  ),

-- Join the list of Shifts to the list of Employee Hours
EmpShiftList
as
( select * from ShiftList, EmpHours 
where InTime<=EndTime and OutTime>=StartTime
),

-- Keep the later   of the shift start time, and the employee in-time
-- Keep the earlier of the shift   end time, and the employee out-time
EmpShifts
as
( select EmployeeID, 
         case when InTime>=StartTime then InTime  else StartTime end as InTime,
         case when OutTime<=EndTime  then OutTime else EndTime   end as OutTime,
         [Type] from EmpShiftList)

-- List the results in order
-- Use MaxRecursion option in case there are more than 100 days 
select * from EmpShifts 
order by EmployeeID, InTime, OutTime
option (maxrecursion 0)

这是另一种不使用数字列表或递归的方法。

注意:经过进一步考虑，以下代码并不总是给出与上面代码相​​同的答案。如果从 InTime 到 OutTime 的时间超过几天，它将失败。这可能不太可能发生，但我认为这是一个错误。试试这个输入:

insert into EmpHours ( EmployeeID, InTime, OutTime ) values
( 7777, '2018-08-10T21:22:23.245', '2018-08-13T09:03:04.056' )

出于历史目的，我将其保留在这里，但我推荐第一个版本。

; -- *** DO NOT USE THIS VERSION *** See notes above. 
with 
-- Get list of dates from table
-- Cast to date to remove time portion
DayListA 
as
( select cast(InTime as date) as DateA from EmpHours
  union
  select cast(OutTime as date) as DateA from EmpHours
),
-- For each such date, add the preceding date.
-- This is to deal with shifts that start the previous day
DayListB
as
( select DateA as DateB from DayListA
  union
  select DateAdd(day,-1,DateA) as DateB from DayListA
),
-- Cast to datetime so we can add hours
-- Remove duplicates
DayList
as
( select distinct cast(DateB as datetime) as WorkDate from DayListB
),
-- Create a list of shifts starting on those dates
ShiftList
as
( select dateadd(hour, 7,WorkDate) as StartTime, -- from 7 AM to 10 PM
         dateadd(hour,22,WorkDate) as EndTime,
         'day' as [Type]
         from DayList
  union
  select dateadd(hour,22,WorkDate) as StartTime, -- from 10 PM to 7 AM of the next day
         dateadd(hour,31,WorkDate) as EndTime,
         'night' as [Type]
         from DayList  ),

-- Join the list of Shifts to the list of Employee Hours
EmpShiftList
as
( select * from ShiftList, EmpHours 
where InTime<=EndTime and OutTime>=StartTime
),

-- Keep the later   of the shift start time, and the employee in-time
-- Keep the earlier of the shift   end time, and the employee out-time
EmpShifts
as
( select EmployeeID, 
         case when InTime>=StartTime then InTime else StartTime end as InTime,
         case when OutTime<=EndTime then OutTime else EndTime end as OutTime,
         [Type] from EmpShiftList)

-- List the results in order
select * from EmpShifts 
order by EmployeeID, InTime, OutTime