T-SQL:计算日期时间差距

Question

代码：

DROP TABLE IF EXISTS #LookupDates ;
CREATE TABLE #LookupDates (StartDate DATETIME, EndDate DATETIME)
INSERT INTO #LookupDates
VALUES
( '2019-12-01 08:00:00', '2019-12-01 16:00:00' )
, ( '2019-12-02 10:00:00', '2019-12-02 18:00:00' )
, ( '2019-12-03 08:30:00', '2019-12-03 16:30:00' )
, ( '2019-12-04 08:00:00', '2019-12-04 16:00:00' )
, ( '2019-12-05 08:00:00', '2019-12-05 16:00:00' )
, ( '2019-12-06 09:00:00', '2019-12-06 17:00:00' )
, ( '2019-12-07 08:30:00', '2019-12-07 16:30:00' )
, ( '2019-12-08 11:00:00', '2019-12-08 15:30:00' )
, ( '2019-12-09 08:30:00', '2019-12-09 16:30:00' )
, ( '2019-12-10 10:30:00', '2019-12-10 16:45:00' )
DROP TABLE IF EXISTS #Data ;
CREATE TABLE #Data (EmpId INT, ClockInDate DATETIME, ClockOutDate DATETIME)
INSERT INTO #Data
VALUES
( 1, '2019-12-01 07:00:00', '2019-12-01 07:30:00' ) -- Completely before
, ( 1, '2019-12-01 18:00:00', '2019-12-01 22:00:00' ) -- Completely after
, ( 1, '2019-12-02 09:30:00', '2019-12-02 18:00:00' ) -- Clockin before
, ( 1, '2019-12-03 09:00:00', '2019-12-03 16:30:00' ) -- Clockin after
, ( 1, '2019-12-04 08:00:00', '2019-12-04 15:45:00' ) -- Clockout before
, ( 1, '2019-12-05 08:00:00', '2019-12-05 17:15:00' ) -- Clockout after
, ( 1, '2019-12-06 08:40:00', '2019-12-06 16:45:00' ) -- Clockin before & Clockout before
, ( 1, '2019-12-07 08:25:00', '2019-12-07 17:05:00' ) -- Clockin before & Clockout after
, ( 1, '2019-12-08 12:00:00', '2019-12-08 15:15:00' ) -- Clockin after & Clockout before
, ( 1, '2019-12-09 08:30:01', '2019-12-09 16:30:27' ) -- Clockin after & Clockout before
, ( 1, '2019-12-10 10:30:00', '2019-12-10 16:45:00' ) -- Clockin on-time & Clockout on-time

描述：

LookupDates包含公司的每日营业时间。实际表具有额外的天数数据。

数据包含每个员工的打卡/下班打卡时间戳。实际表具有额外的 emps 和天数的数据。

目标：确定 #Data 条目是否偏离当天的营业时间。

• EMP打卡早了吗？多少秒？
• EMP打卡晚了吗？多少秒？
• emp 打卡早了吗？多少秒？
• emp 打卡迟到了吗？多少秒？

期望输出：

EmpId       LookupStartDate         ClockInDate             IsClockInBefore     ClockInBeforeTimeInSec      IsClockInAfter      ClockInAfterTimeInSec   LookupEndDate           ClockOutDate            IsClockOutBefore    ClockOutBeforeTimeInSec     IsClockOutAfter     ClockOutAfterTimeInSec
1           '2019-12-01 08:00:00'   '2019-12-01 07:00:00'   1                   3600                        0                   0                       '2019-12-01 16:00:00'   '2019-12-01 07:30:00'   1                   30600                       0                   0
1           '2019-12-01 08:00:00'   '2019-12-01 18:00:00'   0                   0                           1                   36000                   '2019-12-01 16:00:00'   '2019-12-01 22:00:00'   0                   0                           1                   21600
1           '2019-12-02 10:00:00'   '2019-12-02 09:30:00'   1                   1800                        0                   0                       '2019-12-02 18:00:00'   '2019-12-02 18:00:00'   0                   0                           0                   0
1           '2019-12-03 08:30:00'   '2019-12-03 09:00:00'   0                   0                           1                   1800                    '2019-12-03 16:30:00'   '2019-12-03 16:30:00'   0                   0                           0                   0
1           '2019-12-04 08:00:00'   '2019-12-04 08:00:00'   0                   0                           0                   0                       '2019-12-04 16:00:00'   '2019-12-04 15:45:00'   1                   900                         0                   0
1           '2019-12-05 08:00:00'   '2019-12-05 08:00:00'   0                   0                           0                   0                       '2019-12-05 16:00:00'   '2019-12-05 17:15:00'   0                   0                           1                   4500
1           '2019-12-06 09:00:00'   '2019-12-06 08:40:00'   1                   1200                        0                   0                       '2019-12-06 17:00:00'   '2019-12-06 16:45:00'   1                   900                         0                   0
1           '2019-12-07 08:30:00'   '2019-12-07 08:25:00'   1                   300                         0                   0                       '2019-12-07 16:30:00'   '2019-12-07 17:05:00'   0                   0                           1                   2100
1           '2019-12-08 11:00:00'   '2019-12-08 12:00:00'   0                   0                           1                   3600                    '2019-12-08 15:30:00'   '2019-12-08 15:15:00'   1                   900                         0                   0
1           '2019-12-09 08:30:00'   '2019-12-09 08:30:01'   0                   0                           1                   1                       '2019-12-09 16:30:00'   '2019-12-09 16:30:27'   0                   0                           1                   27
1           '2019-12-10 10:30:00'   '2019-12-10 10:30:00'   0                   0                           0                   0                       '2019-12-10 16:45:00'   '2019-12-10 16:45:00'   0                   0                           0                   0

Answer 1

select
#Data.EmpId
,#LookupDates.StartDate as LookupStartDate
,#Data.ClockInDate
,case when #Data.ClockInDate < #LookupDates.StartDate then 1 else 0 end as IsClockInBefore
,case when #Data.ClockInDate < #LookupDates.StartDate then datediff(second, #Data.ClockInDate, #LookupDates.StartDate) else 0 end as ClockInBeforeTimeInSec
,case when #Data.ClockInDate > #LookupDates.StartDate then 1 else 0 end as IsClockInAfter
,case when #Data.ClockInDate > #LookupDates.StartDate then datediff(second, #LookupDates.StartDate, #Data.ClockInDate) else 0 end as ClockInAfterTimeInSec
,#LookupDates.EndDate as LookupEndDate
,#Data.ClockOutDate
,case when #Data.ClockOutDate < #LookupDates.EndDate then 1 else 0 end as IsClockOutBefore
,case when #Data.ClockOutDate < #LookupDates.EndDate then datediff(second, #Data.ClockOutDate, #LookupDates.EndDate) else 0 end as ClockOutBeforeTimeInSec
,case when #Data.ClockOutDate > #LookupDates.EndDate then 1 else 0 end as IsClockOutAfter
,case when #Data.ClockOutDate > #LookupDates.EndDate then datediff(second, #LookupDates.EndDate, #Data.ClockOutDate) else 0 end as ClockOutAfterTimeInSec
from
#LookupDates
inner join
#Data on cast(#LookupDates.StartDate as date) = cast(#Data.ClockInDate as date) --assuming the clock in is always the same date as the start date

Answer 2

假设查找日期和数据中的记录不会分布在几天内，则可以在当天加入。剩下的只是一系列case表达式和一点日期算术：

select
d.EmpId,
d.StartDate,
l.ClockInDate,
case when d.StartDate < l.ClockInDate 
then 1 
else 0 
end IsClockInBefore,
case when d.StartDate < l.ClockInDate 
then datediff(second, ClockInDate, StartDate) 
else 0 
end ClockInBeforeTimeInSec,
case when d.StartDate > l.ClockInDate 
then 1 
else 0 
end IsClockInAfter,
case when d.StartDate > l.ClockInDate 
then datediff(second, StartDate, ClockInDate) 
else 0 
end ClockInAfterTimeInSec,
d.EndDate,
l.ClockOutDate,
case when d.EndDate < l.ClockOutDate 
then 1 
else 0 
end IsClockOutBefore,
case when d.EndDate < l.ClockOutDate 
then datediff(second, ClockOutDate, StartDate) 
else 0 
end ClockOutBeforeTimeInSec,
case when d.EndDate > l.ClockOutDate 
then 1 
else 0 
end IsClockOutAfter,
case when d.EndDate > l.ClockOutDate 
then datediff(second, StartDate, ClockOutDate) 
else 0 
end ClockOutAfterTimeInSec
from #Data d
inner join #LookupDates l 
on cast(d.ClockInDate as date) = cast(l.StartDate as date)

Answer 3

不是不同的方法。
只是尝试了一些不同的东西。
(好吧，我承认。我真的很无聊。

SELECT
io.EmpId,
lk.StartDate AS LookupStartDate,
io.ClockInDate,
CAST(  IIF(io.ClockInDate < lk.StartDate,1,0) AS BIT) AS IsClockInBefore,
(CASE WHEN io.ClockInDate < lk.StartDate
THEN DATEDIFF(SECOND, io.ClockInDate, lk.StartDate) 
ELSE 0 END) AS ClockInBeforeTimeInSec,
CAST(  IIF(io.ClockInDate > lk.StartDate, 1, 0) AS BIT) AS IsClockInAfter,
(CASE WHEN io.ClockInDate > lk.StartDate
THEN DATEDIFF(SECOND, lk.StartDate, io.ClockInDate) 
ELSE 0 END) AS ClockInAfterTimeInSec,
lk.EndDate AS LookupEndDate,
io.ClockOutDate,
CAST(  IIF(io.ClockOutDate < lk.EndDate,1,0) AS BIT) AS IsClockOutBefore,
(CASE WHEN io.ClockOutDate < lk.EndDate
THEN DATEDIFF(SECOND, io.ClockOutDate, lk.EndDate) 
ELSE 0 END) AS ClockOutBeforeTimeInSec,
CAST(  IIF(io.ClockOutDate > lk.EndDate,1,0) AS BIT) AS IsClockOutAfter,
(CASE WHEN io.ClockOutDate > lk.EndDate
THEN DATEDIFF(SECOND, lk.EndDate, io.ClockOutDate) 
ELSE 0 END) AS ClockOutAfterTimeInSec
FROM #Data AS io
JOIN #LookupDates AS lk
ON CONVERT(DATE, lk.StartDate) = CONVERT(DATE, io.ClockInDate)
ORDER BY io.EmpId, io.ClockInDate;
GO

企业识别 |查找开始日期 |时钟输入日期 |打卡前 |秒前打卡 |打卡后 |时钟在秒后时间 |查找结束日期 |时钟输出日期 |打卡前 |打卡前时间秒 |是时钟输出后 |ClockOutAfterTimeInSec ----: |:------------------ |:------------------ |:-------------- |---------------------: |:------------- |--------------------: |:------------------ |:------------------ |:--------------- |----------------------: |:-------------- |---------------------: 1 |01/12/2019 08：00：00 |01/12/2019 07：00：00 |真 |                  3600 |假 |                    0 |01/12/2019 16：00：00 |2019/01/12 07：30：00 |真 |                  30600 |假 |                     0 1 |01/12/2019 08：00：00 |01/12/2019 18：00：00 |假 |                     0 |真 |                36000 |01/12/2019 16：00：00 |01/12/2019 22：00：00 |假 |                      0 |真 |                 21600 1 |2019/02/12 10：00：00 |2019/02/12 09：30：00 |真 |                  1800年 |假 |                    0 |02/12/2019 18：00：00 |02/12/2019 18：00：00 |假 |                      0 |假 |                     0 1 |2019/03/12 08：30：00 |03/12/2019 09：00：00 |假 |                     0 |真 |                 1800年 |2019-03-12 16：30：00 |2019-03-12 16：30：00 |假 |                      0 |假 |                     0 1 |04/12/2019 08：00：00 |04/12/2019 08：00：00 |假 |                     0 |假 |                    0 |04/12/2019 16：00：00 |2019-04-12 15：45：00 |真 |                    900 |假 |                     0 1 |05/12/2019 08：00：00 |05/12/2019 08：00：00 |假 |                     0 |假 |                    0 |05/12/2019 16：00：00 |2019-05-12 17：15：00 |假 |                      0 |真 |                  4500 1 |2019-06-12 09：00：00 |2019-06-12 08：40：00 |真 |                  1200 |假 |                    0 |2019/06/12 17：00：00 |2019/06/12 16：45：00 |真 |                    900 |假 |                     0 1 |2019/07/12 08：30：00 |2019-07-12 08：25：00 |真 |                   300 |假 |                    0 |2019/07/12 16：30：00 |2019/07/12 17：05：00 |假 |                      0 |真 |                  2100 1 |08/12/2019 11：00：00 |2019/08/12 12：00：00 |假 |                     0 |真 |                 3600 |2019-12-08 15：30：00 |2019-12-08 15：15：00 |真 |                    900 |假 |                     0 1 |2019/09/12 08：30：00 |2019-09-12 08：30：01 |假 |                     0 |真 |                    1 |2019/09/12 16：30：00 |2019-09-12 16：30：27 |假 |                      0 |真 |                    27 1 |2019/10/12 10：30：00 |2019/10/12 10：30：00 |假 |                     0 |假 |                    0 |2019/10/12 16：45：00 |2019/10/12 16：45：00 |假 |                      0 |假 |                     0

db<>小提琴在这里

Answer 4

我想您所需要的只是不同列数的多个相同大小写。您的查询中没有其他内容。

Select case when 
checkindatetime<estimated then
1 else 0 end as "CheckedInearly" ,
Abs(checkindatetime-estimated) 
.....same logic for other columns
From table