如何比较具有不同行数的日期列?

Question

我想做一个SSRS报告。报告主要是两个表之间的简单连接。但是，有四个日期列跟踪来自两个不同系统的时钟输入和输出(表1中的两列和表2中的一列)。我需要对这些日期列进行比较。这些列每天可以有多个输入和输出。

例如，如果一个系统上有一个时钟，那么在一个小时内，第二个系统上应该有另一个时钟。我的问题在于，当有不同数量的时钟时，我最终很难比较结果，因为一个系统的输入和输出不会完全相同。

表1 (badge, clockkin, clockOut)

(12345, '2021-09-01 08:00:00.000', '2021-09-01 16:30:00.000'),
(34567, '2021-09-01 08:00:00.000', '2021-09-01 17:00:00.000'),
(45678, '2021-09-01 07:00:00.000', '2021-09-01 13:17:00.000'),
(45678, '2021-09-01 12:59:00.000', '2021-09-01 20:34:00.000'),
(67890, '2021-09-01 10:00:00.000', '2021-09-01 18:21:00.000'),

表2 (Badge, clockTime, Reason)

(12345, '2021-09-01 07:45:00.000', 'In'),
(12345, '2021-09-01 12:00:00.000', 'Out'),
(12345, '2021-09-01 12:41:00.000', 'In'),
(34567, '2021-09-01 08:00:00.000', 'In'),
(45678, '2021-09-01 07:00:00.000', 'In'),
(45678, '2021-09-01 12:59:00.000', 'Out'),
(67890, '2021-09-01 10:00:00.000', 'In'),

所以当我把它们连接起来的时候，它可能看起来像:

12345, 2021-09-01 8:00:00, 2021-09-01 11:30:00, 2021-09-01 07:45:00, 2021-09-01 12:00:00
12345, 2021-09-01 8:00:00, 2021-09-01 11:30:00, 2021-09-01 12:41:00, 2021-09-01 12:00:00

我的目标是有一个额外的列，根据时间标记异常。一个例外可能是，如果表2在表1条目的一个小时内没有时钟。对于行，这将在行1上正常工作，但在行2上将是一个异常，即使它不需要，因为已经有一个时钟被考虑在内。

我如何加入/显示我的数据或处理异常，使它们在这种情况下工作?是在我的查询中解决好还是在我的报告中解决好?

如果我解释得不好或遗漏了什么，请提前道歉。我有点累了。

Answer 1

您可以使用EXISTS和DATEDIFF来查找问题，使用不同的CASE表达式来查找不同的问题(如果您可以使用多列)或使用一个大的CASE表达式来查找所有问题，如果您需要它在一列中。这是你的数据(修改ClockTime为67890到12:00:00，因为你没有任何1小时的例外)。

DECLARE @t1 TABLE (Badge VARCHAR(10) NOT NULL, ClockIn DATETIME NOT NULL, ClockOut DATETIME)
INSERT INTO @t1
VALUES
(12345, '2021-09-01 08:00:00.000', '2021-09-01 16:30:00.000'),
(34567, '2021-09-01 08:00:00.000', '2021-09-01 17:00:00.000'),
(45678, '2021-09-01 07:00:00.000', '2021-09-01 13:17:00.000'),
(45678, '2021-09-01 12:59:00.000', '2021-09-01 20:34:00.000'),
(67890, '2021-09-01 10:00:00.000', '2021-09-01 18:21:00.000')
DECLARE @t2 TABLE (Badge VARCHAR(10) NOT NULL, ClockTime DATETIME NOT NULL, Reason VARCHAR(20) NOT NULL)
INSERT INTO @t2
VALUES
(12345, '2021-09-01 07:45:00.000', 'In'),
(12345, '2021-09-01 12:00:00.000', 'Out'),
(12345, '2021-09-01 12:41:00.000', 'In'),
(34567, '2021-09-01 08:00:00.000', 'In'),
(45678, '2021-09-01 07:00:00.000', 'In'),
(45678, '2021-09-01 12:59:00.000', 'Out'),
(67890, '2021-09-01 12:00:00.000', 'In')
SELECT DISTINCT 
t1.Badge,
t1.ClockIn,
CASE WHEN EXISTS (SELECT 1 
FROM @t2 t2
WHERE ((DATEDIFF(MINUTE,t1.ClockIn,t2.ClockTime) < 60))
AND t2.Badge = t1.Badge
AND YEAR(t2.ClockTime) * 100 + MONTH(t2.ClockTime) + DAY(t2.ClockTime) 
= YEAR(t1.ClockIn) * 100 + MONTH(t1.ClockIn)+ DAY(t1.ClockIn))
THEN 'Ok' ELSE 'NoClock' END AS Exception
FROM @t1 t1

异常th>好好好好NoClock