sql - 如何通过查看过去的签到时间来区分白类和夜类？

Question

目前，该报告使用预定义的时间。但有时员工会超时工作，他们会偷偷进入下一个类次报告。如何修改报告查询以查看过去的登录时间以避免这种情况？在 TIMEATT 列中，1 是入口，2 是导出。浅蓝色突出显示正确的条目，黄色表示错误的条目。

29 日的白类报告有“完成，简”从夜类开始的进入时间

29 日的夜类报告有白类的“Do，Jone's”进入时间

下面的查询创建了一个临时表，其中包含与屏幕截图中相同的数据。

CREATE TABLE #temptable ( [Company] nvarchar(60), [ID] int, [NAME] nvarchar(130), [TIMEATT] int, [Time_CST] datetime )
INSERT INTO #temptable
values
( N'Company Ltd.', 11111, N'Done, Jane', 1, N'2019-04-28T18:00:27' ), 
( N'Company Ltd.', 11111, N'Done, Jane', 1, N'2019-04-28T18:00:28' ), --Sometimes people tap their card twice 
( N'Company Ltd.', 11111, N'Done, Jane', 2, N'2019-04-29T06:00:55' ),  
( N'Company Ltd.', 11111, N'Done, Jane', 1, N'2019-04-29T06:01:55' ),  
( N'Company Ltd.', 11111, N'Done, Jane', 2, N'2019-04-29T06:04:55' ),  
( N'Company Ltd.', 11111, N'Done, Jane', 1, N'2019-04-29T18:00:27' ), 
( N'Company Ltd.', 11111, N'Done, Jane', 2, N'2019-04-30T06:13:55' ),  
( N'Company Ltd.', 22222, N'Do, Jone', 1, N'2019-04-29T06:20:17' ), 
( N'Company Ltd.', 22222, N'Do, Jone', 2, N'2019-04-29T06:47:12' ), 
( N'Company Ltd.', 22222, N'Do, Jone', 1, N'2019-04-29T10:33:33' ), 
( N'Company Ltd.', 22222, N'Do, Jone', 2, N'2019-04-29T18:05:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 1, N'2019-04-29T18:06:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 2, N'2019-04-29T18:09:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 1, N'2019-04-30T06:05:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 2, N'2019-04-30T16:05:33' )
-- Test table
-- 1 is entry and 2 is exit. Sometimes people double swipe their cards which results in two 1 entries.
select * from #temptable as T

--Report start
set deadlock_priority low;
declare @shift varchar(10) = 'night';  --Option to switch between day and night
declare @reportdate datetime = '2019-04-29'; --Date to be ran
declare @starttime datetime;
declare @endtime datetime;

select @starttime = (case
                         when @shift = 'day' then
                             convert(datetime, @reportdate) + cast('04:00:00.000' as datetime)
                         when @shift = 'night' then
                             convert(datetime, @reportdate) + cast('16:00:00.000' as datetime)
                     end
                    );
select @endtime = (case
                       when @shift = 'day' then
                           convert(datetime, @reportdate) + cast('23:59:59.000' as datetime)
                       when @shift = 'night' then
                           convert(datetime, dateadd(d, 1, @reportdate)) + cast('11:59:59.000' as datetime)
                   end
                  );


select Company
     , NAME
     , EmpID
     , startTime
     , endTime
     , sum(datediff(second, startTime, endTime) / 3600.0) as HrsWorked
from
( -- sub query to get matching exit time for each entry if it exists
    select Company
         , NAME
         , ID                                                                 as EmpID
         , Time_CST                                                           as startTime
         , lead(Time_CST, 1, null) over (partition by NAME order by Time_CST) as endTime
         , TIMEATT
         , Time_CST
    from
    ( -- subquery to exclude duplicate records
        select *
        from
        (
            select *
            from
            ( -- subquery to identify records to ignore
                select Company
                     , NAME
                     , ID
                     , TIMEATT
                     , Time_CST
                     , case lead(TIMEATT, 1, 0) over (partition by NAME order by Time_CST)
                           when TIMEATT then
                               1
                           else
                               0
                       end as Exclude                                    
                from  #temptable 

            ) a
            where Exclude = 0

        ) t
    ) n
) z
where TIMEATT = 1 -- filter so left column is always entry time.
      and startTime >= @starttime
      and endTime <= @endtime
--and Company in (@contractornames)
group by z.Company
       , z.NAME
       , z.EmpID
       , z.startTime
       , z.endTime
order by Company
       , NAME
       , startTime


--DROP TABLE #temptable

Answer 1

首先，我更改了您的查询以使用 CTE 而不是 3 级子查询。使其更易于阅读。

为了识别错误的打洞，我在您的查询中扩展了 CASE 语句，该语句识别重复的打洞并将它们标记为排除。使用 LEAD 功能，我检查了下一次冲压是否在 10 分钟内。如果是，则将其标记为排除。

显然，此解决方案并不完美，但可以让您到达需要的地方，并对错误有合理的容忍度。津贴窗口可以扩大或缩小。

更新:您提供的新数据集超过了 10 分钟的限额。我把它提高到 20。同样，这里有一个错误容忍度。

我还调整了 InOut 查询以不使用任何以 TIMEATT = 1 作为开始时间的查询。这确保了打洞永远不会作为开始时间进入数据集。

最后，我向输出查询添加了一个条件，以确保开始时间在请求的报告日期。

IF OBJECT_ID('tempdb.dbo.#temptable', 'U') IS NOT NULL
    DROP TABLE #temptable;

CREATE TABLE #temptable ( [Company] nvarchar(60), [ID] int, [NAME] nvarchar(130), [TIMEATT] int, [Time_CST] datetime )
INSERT INTO #temptable VALUES
( N'Company Ltd.', 11111, N'Done, Jane', 1, N'2019-04-28T18:00:27' ), 
( N'Company Ltd.', 11111, N'Done, Jane', 1, N'2019-04-28T18:00:28' ), --Sometimes people tap their card twice 
( N'Company Ltd.', 11111, N'Done, Jane', 2, N'2019-04-29T06:00:55' ),  
( N'Company Ltd.', 11111, N'Done, Jane', 1, N'2019-04-29T06:01:55' ),  
( N'Company Ltd.', 11111, N'Done, Jane', 2, N'2019-04-29T06:04:55' ),  
( N'Company Ltd.', 11111, N'Done, Jane', 1, N'2019-04-29T18:00:27' ), 
( N'Company Ltd.', 11111, N'Done, Jane', 2, N'2019-04-30T06:13:55' ),  
( N'Company Ltd.', 22222, N'Do, Jone', 1, N'2019-04-29T06:20:17' ), 
( N'Company Ltd.', 22222, N'Do, Jone', 2, N'2019-04-29T06:47:12' ), 
( N'Company Ltd.', 22222, N'Do, Jone', 1, N'2019-04-29T10:33:33' ), 
( N'Company Ltd.', 22222, N'Do, Jone', 2, N'2019-04-29T18:05:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 1, N'2019-04-29T18:06:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 2, N'2019-04-29T18:09:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 1, N'2019-04-30T06:05:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 2, N'2019-04-30T16:05:33' )
-- Test table
-- 1 is entry and 2 is exit. Sometimes people double swipe their cards which results in two 1 entries.
select * from #temptable as T

--Report start
set deadlock_priority low;
declare @shift varchar(10) = 'night';  --Option to switch between day and night
declare @reportdate datetime = '2019-04-29'; --Date to be ran
declare @starttime datetime;
declare @endtime datetime;

select @starttime = (case
                         when @shift = 'day' then
                             convert(datetime, @reportdate) + cast('04:00:00.000' as datetime)
                         when @shift = 'night' then
                             convert(datetime, @reportdate) + cast('16:00:00.000' as datetime)
                     end
                    );
select @endtime = (case
                       when @shift = 'day' then
                           convert(datetime, @reportdate) + cast('23:59:59.000' as datetime)
                       when @shift = 'night' then
                           convert(datetime, dateadd(d, 1, @reportdate)) + cast('11:59:59.000' as datetime)
                   end
                  );


WITH NoDoubles AS
    (
    SELECT
        Company
        , [NAME]
        , ID
        , TIMEATT
        , Time_CST
        , CASE
            WHEN LEAD(TIMEATT, 1, 0) OVER (PARTITION BY NAME ORDER BY Time_CST) = TIMEATT THEN 1
            /* Allow for 10 minute grace period for swipes to be excluded */
            WHEN LEAD(Time_CST, 1, 0) OVER (PARTITION BY NAME ORDER BY Time_CST) = '1900-01-01 00:00:00.000' THEN 0
            WHEN LEAD(Time_CST, 1, 0) OVER (PARTITION BY NAME ORDER BY Time_CST) <= DATEADD(MINUTE, 10, Time_CST) THEN 1
            ELSE 0
            END
            AS Exclude
    FROM
        #temptable
    )
     , InOut AS
    (
    SELECT
    Company
    , [NAME]
    , ID                                                                 AS EmpID
    , IIF(TIMEATT = 1, Time_CST, NULL)                                   AS startTime
    , LEAD(Time_CST, 1, NULL) OVER (PARTITION BY NAME ORDER BY Time_CST) AS endTime
    , TIMEATT
    , Time_CST
FROM
    NoDoubles
WHERE
    Exclude = 0
    )

SELECT
    Company
    , [NAME]
    , EmpID
    , startTime
    , endTime
    , SUM(DATEDIFF(SECOND, startTime, endTime) / 3600.0) AS HrsWorked
FROM
    InOut
WHERE
    CAST(startTime AS DATE) = @reportdate
    AND startTime >= @starttime
    AND endTime <= @endtime
GROUP BY
    Company
    , [NAME]
    , EmpID
    , startTime
    , endTime
ORDER BY
    Company
    , [NAME]
    , startTime;

DROP TABLE #temptable