SQL Server查询传入和传出

Question

这是我从DTR设备中保存在Ms sql数据库中的。

ID | Employee_ID | Date | InOutMode -------+-------------+---------------------+----------- 70821 | 104 | 2019-10-11 19:00:00 | 0 70850 | 104 | 2019-10-12 07:01:00 | 1

如果我要分离IN和OUT，假设是这样的：

ID | Employee_ID | IN | OUT -------+-------------+---------------------+----------- 70821 | 104 | 2019-10-11 19:00:00 | 2019-10-12 07:01:00

发生的情况是，我不知道我的查询是否错误。超时时间不是2019-10-12，而是2019-10-11与TIME-IN相同，如下所示：

ID | Employee_ID | IN | OUT -------+-------------+---------------------+----------- 70821 | 104 | 2019-10-11 19:00:00 | 2019-10-11 07:01:00

Answer 1

尝尝这个,

DECLARE @Temp_Table Table
(
    Empoyee_id int,
    [Date] datetime,
    [InOutMode] bit
)

INSERT INTO @Temp_Table
(
    Empoyee_id,[Date],[InOutMode]

)
SELECT 104,'20191011 09:30',1 
UNION ALL
SELECT 104,'20191011 19:30',0 
UNION ALL
SELECT 104,'20191012 09:30',1 
UNION ALL
SELECT 104,'20191012 12:30',0 
UNION ALL
SELECT 104,'20191012 19:00',0 
UNION ALL
SELECT 104,'20191013 09:30',1 
UNION ALL
SELECT 104,'20191013 07:30',0
UNION ALL
SELECT 104,'20191014 09:30',1 

SELECT Empoyee_id,[Date],[In],IIF([In]>[Out],null,[Out]) as [Out]
FROM
(
    SELECT Empoyee_id,CAST([Date] AS DATE) AS [Date],
    MIN(IIF(InOutMode=1,[Date],NULL)) AS [In] ,
    MAX(IIF(InOutMode=0,[Date],NULL)) AS [Out]
    FROM @Temp_Table
    GROUP BY Empoyee_id,CAST([Date] AS DATE)
)A

Answer 2

试试这个：

;
WITH Ins as (
  Select *
  FROM   HR_DTR_Device 
  WHERE  InOutMode = 0
),
Outs as (
  Select *
  FROM   HR_DTR_Device
  WHERE  InOutMode = 1
)
SELECT Ins.ID,
       Ins.Employee_ID,
       Ins.Date as [In],
       (
         SELECT Min(Outs.Date)
         FROM   Outs
         WHERE  Ins.Employee_ID = Outs.Employee_ID
            AND Outs.Date > Ins.Date
       ) as [Out]
FROM   Ins
WHERE  Ins.Employee_ID = '104'

它的作用是：

1. 将Ins和Outs分开，就像它们是独立的数据源一样。使用公用表表达式实际上允许您预定义子查询并为其命名。
2. 对于Ins中的每个记录，将从Outs中查找仍然大于In日期的最小日期。(这假设您的记录是完整的，并且您永远不能有两个日期，因为有人忘记计时了，所以out.)
3. Doesn't假设Out日期是什么时候发生的，只是它晚于In日期(根据定义)。这样，您就不必担心员工是当天晚些时候离开还是第二天早些时候离开(如果您有不同工作的员工，shifts.)
4. Will还会显示员工在哪里打卡但还没有下班的条目。

我认为你最大的错误在这里：

 (SELECT MAX(Date) FROM HR_DTR_Device XX 
 WHERE InOutMode = 1
   AND XX.Employee_ID = AA.Employee_ID
   AND CAST(XX.Date AS DATE) = CAST(AA.Date AS DATE)) AS 'Out'

您将返回同一日历日期(且为Out)上的该员工的最大日期。但是，如果员工工作到第二天早上，日期就会改变！

您可以通过将测试更改为以下代码来修复此问题：

CAST(DATEADD(d, -1, XX.Date) AS DATE) = CAST(AA.Date AS DATE)

..。但是，它只适用于通宵工作的员工，而我的解决方案只是简单地查找员工在打卡后的下一次打卡时间，无论是同一天、第二天还是下周！

如果您喜欢此解决方案，请将其标记为您已接受的解决方案。谢谢。