时间戳

首页 > 解决方案 > 查询以从多个打孔中派生计划

sql - 查询以从多个打孔中派生计划

问题描述

我无法为每位员工构建一个每天只有一个时间表的查询。我正在处理的数据记录了每次打卡时间和每次打卡时间。我使用的查询工作正常，直到我遇到跨越一夜的时间表。下面是我使用的查询和一些示例数据。

SELECT EMPLOYEEID
,min(PUNCHIN) AS PUNCHIN
,max(PUNCHOUT) AS PUNCHOUT
FROM EMPSCHED
WHERE PUNCHIN <> PUNCHOUT 
GROUP BY EMPLOYEEID
,CAST(PUNCHIN AS DATE)
order by EMPID, PUNCHIN

样品表：

EMPLOYEEID  PUNCHIN             PUNCHOUT
DGHAG   2020-07-10 20:00:00.000 2020-07-11 01:45:00.000
DGHAG   2020-07-11 01:45:00.000 2020-07-11 02:15:00.000
DGHAG   2020-07-11 02:15:00.000 2020-07-11 06:30:00.000
DGHAG   2020-07-11 06:30:00.000 2020-07-11 06:30:00.000
DGHAG   2020-07-11 20:00:00.000 2020-07-11 23:30:00.000
DGHAG   2020-07-11 23:30:00.000 2020-07-12 00:00:00.000
DGHAG   2020-07-12 00:00:00.000 2020-07-12 06:30:00.000
DGHAG   2020-07-12 06:30:00.000 2020-07-12 06:30:00.000
DGHAG   2020-07-12 20:00:00.000 2020-07-13 02:15:00.000
DGHAG   2020-07-13 02:15:00.000 2020-07-13 02:45:00.000
DGHAG   2020-07-13 02:45:00.000 2020-07-13 06:30:00.000
DGHAG   2020-07-13 06:30:00.000 2020-07-13 06:30:00.000
DGHFA   2020-07-08 08:30:00.000 2020-07-08 13:30:00.000
DGHFA   2020-07-08 13:30:00.000 2020-07-08 13:30:00.000
DGHFA   2020-07-09 08:30:00.000 2020-07-09 13:30:00.000
DGHFA   2020-07-09 13:30:00.000 2020-07-09 13:30:00.000
DGHFA   2020-07-11 08:30:00.000 2020-07-11 13:30:00.000
DGHFA   2020-07-11 13:30:00.000 2020-07-11 13:30:00.000

目标是为每个班次创建一个记录，将日程安排到一天的第一次打卡到班次的最后一次打卡，无论是同一天还是下一天。

创建表代码：

CREATE TABLE [dbo].[EMPSCHED](
    [EMPLOYEEID] [nvarchar](50) NULL,
    [PUNCHIN] [datetime] NULL,
    [PUNCHOUT] [datetime] NULL
)

标签： sqltsql

解决方案

以下查询将检测计划中的所有间隙（休息或轮班结束），而不管轮班的长度（它可以跨越多天）。

WITH CTE AS (
  SELECT 
    EMPLOYEEID
    ,LAG(PUNCHOUT) OVER (PARTITION BY EMPLOYEEID ORDER BY PUNCHIN ASC) as last_punchout
    ,PUNCHIN
    ,PUNCHOUT
    ,LEAD(PUNCHIN) OVER (PARTITION BY EMPLOYEEID ORDER BY PUNCHIN ASC) as next_punchin
  FROM EMPSCHED
  WHERE PUNCHIN <> PUNCHOUT 
)
SELECT 
  EMPLOYEEID
  ,PUNCHIN
  ,PUNCHOUT
FROM CTE 
WHERE last_punchout <> PUNCHIN 
OR PUNCHOUT <> next_punchin 
OR last_punchout is null 
OR next_punchin is null

你可以在这里玩它：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b651d6614d14ccec2f8da4d63f5f6a2f

推荐阅读