当前登录的员工的SQL列表

IntoTomorrow 发表于 Dev

明天

我正在尝试获取当前进入我们工厂的员工列表。计时事务使用以下命令更新计时表：“ dayreviewinoutflag = 0”用于计时输入，“ dayreviewinoutflag = 1”用于计时输出，我想回顾过去的72小时，以获取没有上次计时的员工的最新IN计时OUT时钟。

我觉得下面的查询差不多了，但是....

SELECT cc.* 
FROM   clocking cc 
       INNER JOIN (SELECT employeecode, 
                          Max(clockingdatetimeunrounded) AS MaxDateTime, 
                          CASE 
                            WHEN clocking.dayreviewinoutflag = 0 THEN Max( 
                            clockingdatetimeunrounded) 
                            ELSE NULL 
                          END                            AS InDateTime, 
                          CASE 
                            WHEN clocking.dayreviewinoutflag = 1 THEN Max( 
                            clockingdatetimeunrounded) 
                            ELSE NULL 
                          END                            AS OutDateTime 
                   FROM   clocking 
                   WHERE  clockingdatetimeunrounded >= 
                          Dateadd(hour, -72, Getdate()) 
                   GROUP  BY employeecode) groupedcc 
               ON cc.employeecode = groupedcc.employeecode 
                  AND cc.clockingdatetimeunrounded = groupedcc.maxdatetime 
                  AND groupedcc.indatetime > groupedcc.outdatetime

在这一点上，我遇到了有关聚合函数或GROUP BY的错误，但是如果我尝试将'dayreviewinoutflag'添加到GROUP中，则不会得到任何结果。

对于任何反馈，我们都表示感谢。

下表数据应导致结果列表中不返回EMPLOYEECODE 11307

+--------------+---------------+-------------------------------+-------------------+
| AUTONUMBER    EMPLOYEECODE    CLOCKINGDATETIMEUNROUNDED       DAYREVIEWINOUTFLAG |
+--------------+---------------+-------------------------------+-------------------+
| 208945        11307           2015-11-10 17:31:00.000         1                  |
| 209284        11307           2015-11-11 07:59:00.000         0                  |
| 209372        11307           2015-11-11 17:01:00.000         1                  |
| 210162        11307           2015-11-13 07:30:00.000         0                  |
| 210185        11307           2015-11-13 10:30:00.000         1                  |
+--------------+---------------+-------------------------------+-------------------+

摇摆人

最初的问题是您正在clocking.dayreviewinoutflag查询中使用它，因此它必须包含在您的GROUP BY子句中。

但是，您编写的查询无法实现您的目标，您的内部选择正试图同时获取员工的最后入/出日期时间。您需要将这些查询拆分为单独的查询，然后将它们合并以进行比较：

WITH cte_LastClockIn AS
(
    SELECT employeecode, 
          Max(clockingdatetimeunrounded) AS InDateTime, 
    FROM   clocking 
    WHERE  clockingdatetimeunrounded >= 
          Dateadd(hour, -72, Getdate())
    AND clocking.dayreviewinoutflag = 0
    GROUP  BY employeecode
)

,cte_LastClockOut AS
(
    SELECT employeecode, 
          Max(clockingdatetimeunrounded) AS OutDateTime, 
    FROM   clocking 
    WHERE  clockingdatetimeunrounded >= 
          Dateadd(hour, -72, Getdate())
    AND clocking.dayreviewinoutflag = 1
    GROUP  BY employeecode
)


SELECT 
    cc.* 
FROM clocking cc 

INNER JOIN cte_LastClockIn lci
ON cc.employeecode = lci.employeecode
AND cc.clockingdatetimeunrounded = lci.InDateTime

INNER JOIN cte_LastClockOut lco
ON cc.employeecode = lco.employeecode

WHERE lci.InDateTime > lco.OutDateTime