列表时间与多个班次和跨日期一起工作

Question

我有几个问题有关如何处理SQL 2012中的一些员工计时查询…我上周询问了另一个Quesiton，以确定两次班次之间的时间对。

的很好回应

我们是从销售点软件提供商中喂养数据的，因此我们无法更改数据的格式。

工作日是基于从5:00上午5:00到下一个日历日上午4:59的日期，因此它越过午夜。位置结束过程有点偏移以控制平衡负载，有些是上午5:05，而不是上午5:00。当一天的结束过程结束时，它会将每个人都计时，然后在过程完成后将其回到。 - 员工在30分钟的需要休息时将其钟表时间，因此这些员工一天有2个或更多记录。不确定为什么，但是有几个员工在一天内拥有超过3个记录（imhereeeShiftNumber）。 - 偶尔转移约会。在11:00 pm-上午7:00

我需要报告初始intime和最后的耗时列表以及工作的分钟数。我将不得不将这些与持有员工时间表的表进行比较。该报告不是出于薪资目的，而是用于与预定班次的比较。

我包含了一些由员工分组的示例数据，以及我希望看到的值对右边的评论。我还需要查看轮班中的分钟数。

--drop table #Shift
CREATE TABLE #Shift(
    FKEmployeeNumber int,
    DateOfBusiness datetime,
    FKStoreId int,
    EmployeeShiftNumber int,
    FKJobCodeId int,
    InHour int,
    InMinute int,
    OutHour int,
    OutMinute int)
insert into #Shift ( FKEmployeeNumber, DateOfBusiness, FKStoreId, EmployeeShiftNumber, FKJobCodeId, InHour, InMinute,OutHour,OutMinute)
values
(23761, '11/30/2017', 3013, 0, 1, 17, 39, 21, 30),
(23761, '11/30/2017', 3013, 1, 1, 21, 30, 2, 39),   -- 5:39PM  2:39AM
(23770, '11/30/2017', 3013, 0, 200, 7, 19, 16, 25), -- 7:19AM  4:25PM
(23938, '11/30/2017', 3013, 0, 1, 16, 4, 1, 26),    -- 4:04AM  1:26AM
(24006, '11/30/2017', 3013, 0, 1, 7, 30, 18, 36),
(24006, '11/30/2017', 3013, 1, 1, 18, 36, 18, 40),  -- 7:30AM  6:40PM
(24018, '11/30/2017', 3013, 0, 2, 8, 52, 17, 0),    -- 8:52M  4:00PM
(25176, '11/30/2017', 3013, 0, 200, 15, 59, 20, 1), -- 3:59PM  8:01PM
(25176, '11/30/2017', 3013, 1, 200, 20, 30, 0, 05), -- 8:30PM  12:05AM
(25180, '11/30/2017', 3013, 0, 1, 21, 0, 5, 0),     -- 9:00PM  5:00AM
(25187, '11/30/2017', 3013, 0, 1, 10, 0, 16, 6),    -- 10:00AM  4:06PM
(35189, '11/30/2017', 3013, 0, 1, 16, 58, 2, 4),    -- 4:58PM  2:04AM
(25147, '12/04/2017', 3106, 0, 1, 6, 58, 15, 2),
(25147, '12/04/2017', 3106, 1, 1, 15, 3, 15, 3),    -- 6:58AM  3:03PM
(26291, '12/01/2017', 3118, 1, 200, 23, 15, 5, 5),
(26291, '12/02/2017', 3118, 0, 200, 5, 6, 7, 22),   -- 11:15PM  7:22AM
(26291, '12/03/2017', 3118, 0, 200, 7, 30, 15, 38), -- 7:30AM  3:38PM
(26291, '12/04/2017', 3118, 0, 200, 23, 15, 5, 5),
(26291, '12/05/2017', 3118, 0, 200, 5, 6, 7, 12),   -- 11:15PM  7:12AM
(26291, '12/05/2017', 3118, 1, 200, 23, 15, 5, 5),
(26291, '12/06/2017', 3118, 0, 200, 15, 14, 7, 5)   -- 11:15PM  7:05AM
--Select * from #Shift
SELECT fkstoreid, FKEmployeeNumber AS EmpID, DateOfBusiness AS Date, 
    RIGHT(CONVERT(varchar(30), DATEADD(MINUTE, InTime_Mins, 0), 100), 7) AS InTime,
    RIGHT(CONVERT(varchar(30), DATEADD(MINUTE, OutTime_Mins, 0), 100), 7) AS OutTime,
    MinsWorked
FROM (
    select sh.FKStoreId, sh.FKEmployeeNumber, sh.DateOfBusiness, 
        MIN(sh.InHour*60+InMinute) AS InTime_Mins,
        MAX(sh.OutHour*60+OutMinute) AS OutTime_Mins,
        SUM(((sh.outhour+case when sh.OutHour < sh.InHour then 24 else 0 end)*60 + sh.outminute) -
             (sh.inhour*60 + sh.inminute)) AS MinsWorked
    from #shift sh
    group by sh.FKStoreId,sh.FKEmployeeNumber, sh.DateOfBusiness
) AS derived
order by FKEmployeeNumber, DateOfBusiness

Answer 1

我已经写了一个如何做的样本。

简短地总结了我执行以下步骤：

1. 标记记录应与下一个记录或上一个记录分组，因为它们是同一轮班的一部分。
2. 找到每个班次的开始和结束
3. 加入属于每个班次的员工的所有记录
4. 将每个换班者的每个员工的结果集组小组，并通过将班次内的每个记录的检查和结帐之间的分钟进行总结。

样本查询：

WITH ShiftParts AS (
    SELECT
        FKEmployeeNumber,
        DATEADD(MINUTE, InMinute, DATEADD(HOUR, InHour, DateOfBusiness)) CheckIn, --Datetime of the checkin
        DATEADD(DAY, CASE WHEN OutHour < InHour THEN 1 ELSE 0 END,
            DATEADD(MINUTE, OutMinute, DATEADD(HOUR, OutHour, DateOfBusiness))) CheckOut --Datetime of the checkout (add one day if we crossed midnight).
    FROM #Shift
), 
GroupInfo AS (
    SELECT 
        *,
        CASE 
            WHEN DATEDIFF(MINUTE, LAG(CheckOut, 1, NULL) OVER (PARTITION BY FKEmployeeNumber ORDER BY CheckOut), CheckIn) <= 120 
            THEN 1 ELSE 0 END AS GroupWithPrevious, --Determine whether we want to group this record with the previous
        CASE 
            WHEN DATEDIFF(MINUTE, CheckOut, LEAD(CheckIn, 1, NULL) OVER (PARTITION BY FKEmployeeNumber ORDER BY CheckOut)) <= 120 
            THEN 1 ELSE 0 END AS GroupWithNext --Determine whether we want to group this record with the next
    FROM ShiftParts
), ShiftStartAndEnd AS (
    SELECT
        *,
        CASE WHEN GroupWithNext = 1 THEN LEAD(CheckOut, 1, NULL) OVER (PARTITION BY FKEmployeeNumber ORDER BY CheckOut) ELSE CheckOut END AS FinalCheckOut
    FROM GroupInfo 
    WHERE GroupWithPrevious = 0 OR GroupWithNext = 0 --Only pick beginning and end of a shift
)
SELECT 
    sse.FKEmployeeNumber, 
    sse.CheckIn, 
    sse.FinalCheckOut,
    SUM(DATEDIFF(MINUTE,sp.CheckIn,sp.CheckOut)) AS MinutesWorked
FROM ShiftStartAndEnd sse
    LEFT OUTER JOIN ShiftParts sp ON sp.FKEmployeeNumber = sse.FKEmployeeNumber AND sp.CheckIn >= sse.CheckIn AND sp.CheckOut <= sse.FinalCheckOut
WHERE sse.GroupWithPrevious = 0
GROUP BY sse.FKEmployeeNumber, sse.CheckIn, sse.FinalCheckOut

注意：我认为您的示例数据中的最后两个记录不应分组

(26291, '12/05/2017', 3118, 1, 200, 23, 15, 5, 5),
(26291, '12/06/2017', 3118, 0, 200, 15, 14, 7, 5)   -- 11:15PM  7:05AM

应该是：

(26291, '12/05/2017', 3118, 1, 200, 23, 15, 5, 5),  -- 11:19PM  5:05AM
(26291, '12/06/2017', 3118, 0, 200, 15, 14, 7, 5)   -- 3:14PM  7:05AM