SQL Server 重叠打卡下班和上班的汇总打卡班次

7eumitmz 于 2023-01-20 发布在其他

关注(0)|答案(1)|浏览(123)

我有一个文件，其中包含所有的时间打卡班次为每个员工这样：
| 雇员|开始时间|结束时间|
| - ------|- ------|- ------|
| 一百二十三|2022年10月23日10时40分00秒|2022年10月23日14时00分|
| 一百二十三|2022年10月23日14时00分|2022年10月23日14时30分|
| 一百二十三|2022年10月23日14时35分00秒|2022年10月23日17时7分00秒|
| 五百四十一|2022年10月23日06时50分00秒|2022年10月23日12时00分00秒|
| 五百四十一|2022年10月23日13时00分|2022年10月23日15时30分|
但是，如果上班时间和下班时间在重叠的时间段内，我希望返回每个员工的汇总行，如下所示
| 雇员|开始时间|结束时间|
| - ------|- ------|- ------|
| 一百二十三|2022年10月23日10时40分00秒|2022年10月23日17时7分00秒|
| 五百四十一|2022年10月23日06时50分00秒|2022年10月23日12时00分00秒|
| 五百四十一|2022年10月23日13时00分|2022年10月23日15时30分|
我之所以要这样做，是因为接下来我将对在给定时间段内属于时移的员工进行计数。现在我的查询是对重复员工进行计数，因为存在在同一小时内打卡下班和打卡上班的员工。
这是我计算每小时雇员人数的代码

WITH  Hours AS(
    SELECT 1 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM Hours WHERE HOUR < 24
)
SELECT 
   CAST(Start_Time AS DATE) [DATE], 
   HOUR, 
   COUNT(Emp_Int) AS [Head Count]
FROM 
   Hours
LEFT JOIN 
   TIME_SHIFTS_TABLE T on HOUR BETWEEN DATEPART(HOUR, START_TIME) AND DATEPART(HOUR, END_TIME)
GROUP BY 
   CAST(Start_Time AS DATE), HOUR
ORDER BY 
   CAST(Start_Time AS DATE), HOUR asc

这个连接可以工作，但是当员工在同一小时内注销并重新登录时，它会计算重复的次数。

sql-server

来源：https://stackoverflow.com/questions/75138531/summarized-punch-shifts-for-overlapping-clock-outs-and-ins

1条答案

按热度按时间

aamkag611#

这是一个"空白和孤岛"问题。处理这类任务的一种方法是

首先用运行总和生成所需的分区，
然后应用具有MIN(start_time)和MAX(end_time)的聚合。

您可以通过以下方式计算累计金额：

使用DATEDIFF检查当前开始时间和先前结束时间之间的差值何时大于59分钟，因此如果是这种情况，则使用1标记
在此标志上使用SUM窗口函数，用于您的每个员工

然后，您可以在新创建的分区上运行聚合。

WITH cte AS (
    SELECT *,
           CASE WHEN DATEDIFF(mi, LAG(end_time) OVER(PARTITION BY employee ORDER BY start_time), start_time) >= 60 
                THEN 1 ELSE 0 
           END AS change_partition
    FROM tab
), cte2 AS (
    SELECT *, SUM(change_partition) OVER(PARTITION BY employee ORDER BY start_time) AS partitions
    FROM cte
)
SELECT employee, MIN(start_time) AS start_time, MAX(end_time) AS end_time 
FROM cte2
GROUP BY employee, partitions

检查here演示。

赞(0）回复(0）举报 2023-01-20

我来回答

SQL Server 重叠打卡下班和上班的汇总打卡班次

1条答案

相关问题

热门标签

最新问答