时间戳

首页 > 解决方案 > 选择先进后出时间 - 不同的日期 - 从数据手指

sql - 选择先进后出时间 - 不同的日期 - 从数据手指

问题描述

这是我的数据指针表，[dbo].[tFPLog]

CardID  Date        Time   TransactionCode
100     2020-09-01  08:00  IN
100     2020-09-01  17:00  OUT 
100     2020-09-01  17:10  OUT 
200     2020-09-01  16:00  IN
200     2020-09-02  02:00  OUT
200     2020-09-02  02:15  OUT
100     2020-09-02  07:00  IN
100     2020-09-02  16:00  OUT
200     2020-09-02  09:55  IN
200     2020-09-02  10:00  IN
200     2020-09-02  21:00  OUT

条件

  1. 假设员工将在同一天/第二天进出。
  2. 假设员工同一天/第二天会有多个进出。所以需要first IN 和 Last Out。
  3. 持续时间 = (FirstInTime - LastOutTime)

我使用查询得到的当前结果：

WITH CTE AS(
SELECT CardID,
       [Date] AS DateIn,
       MIN(CASE TransactionCode WHEN 'In' THEN [time] ELSE '23:59:59.999' END) AS TimeIn, --'23:59:59.999' as we are after the MIN, and NULL is the lowest value
       [Date] AS DateOut,
       MAX(CASE TransactionCode WHEN 'Out' THEN [time] END) AS TimeOut
FROM YourTable
GROUP BY CardID, [Date])
SELECT C.DateIn,
   C.TimeIn,
   C.DateOut,
   C.TimeOut,
   DATEADD(MINUTE,DATEDIFF(MINUTE,C.TimeIn,C.TimeOut),CONVERT(time(0),'00:00:00')) AS Duration
FROM CTE C;

=====当前结果======

CardID  DateIN      TimeIN  DateOUT     TimeOUT  Duration
100     2020-09-01  08:00   2020-09-01  17:10    09:10
200     2020-09-01  16:00   ?           ?        ?
100     2020-09-02  07:00   2020-09-02  16:00    09:00
200     2020-09-02  09:55   2020-09-02  21:00    11:05

=====需要的结果===== 我想要这个结果。

CardID  DateIN      TimeIN  DateOUT     TimeOUT  Duration
100     2020-09-01  08:00   2020-09-01  17:10    09:10
200     2020-09-01  16:00   2020-09-02  02:15    10:15
100     2020-09-02  07:00   2020-09-02  16:00    09:00
200     2020-09-02  09:55   2020-09-02  21:00    11:05

如何在第二天获得 DateOUT 和 TimeOUT？条件是先入后出。请帮忙，提前谢谢你。

标签： sqlsql-serverdatetimepivot

解决方案

这看起来像你真的过于复杂的问题。只需使用一些条件聚合，然后在几分钟内得到差异：

WITH CTE AS(
    SELECT CardID,
           [Date] AS DateIn,
           MIN(CASE TransactionCode WHEN 'In' THEN [time] ELSE '23:59:59.999' END) AS TimeIn, --'23:59:59.999' as we are after the MIN, and NULL is the lowest value
           [Date] AS DateOut,
           MAX(CASE TransactionCode WHEN 'Out' THEN [time] END) AS TimeOut
    FROM YourTable
    GROUP BY CardID, [Date])
SELECT C.DateIn,
       C.TimeIn,
       C.DateOut,
       C.TimeOut,
       DATEADD(MINUTE,DATEDIFF(MINUTE,C.TimeIn,C.TimeOut),CONVERT(time(0),'00:00:00')) AS Duration
FROM CTE C;

这假设那[date]是一个date并且[time]是一个time（因为毕竟，这就是他们所谓的......）。

旁注：当它们总是具有相同的值时，似乎有些多余的 aDateIn和列。DateOut还不如有一个[Date]专栏。

或者，你实际上是在追求这个？

WITH CTE AS(
    SELECT CardID,
           [Date] AS DateIn,
           [Time] AS TimeIn,
           LEAD([Date]) OVER (PARTITION BY CardID ORDER BY [Date], [Time]) AS DateOut,
           LEAD([Time]) OVER (PARTITION BY CardID ORDER BY [Date], [Time]) AS TimeOut,
           TransactionCode
    FROM dbo.YourTable)
SELECT C.DateIn,
       C.TimeIn,
       C.DateOut,
       C.TimeOut
FROM CTE C
WHERE TransactionCode = 'IN';

请注意，如果是这种情况，您实际上最好将值存储在单个列中作为/ ，[date]而不是单独的；因为这些值显然没有区别。[time]datetimedatetime2

基于（希望）最终目标职位：

WITH VTE AS(
    SELECT *
    FROM (VALUES(100,CONVERT(date,'20200901'),CONVERT(time(0),'08:00:00'),'IN'),
                (100,CONVERT(date,'20200901'),CONVERT(time(0),'17:00:00'),'OUT'),
                (100,CONVERT(date,'20200901'),CONVERT(time(0),'17:10:00'),'OUT'),
                (200,CONVERT(date,'20200901'),CONVERT(time(0),'16:00:00'),'IN'),
                (200,CONVERT(date,'20200902'),CONVERT(time(0),'02:00:00'),'OUT'),
                (200,CONVERT(date,'20200902'),CONVERT(time(0),'02:15:00'),'OUT'),
                (100,CONVERT(date,'20200902'),CONVERT(time(0),'07:00:00'),'IN'),
                (100,CONVERT(date,'20200902'),CONVERT(time(0),'16:00:00'),'OUT'),
                (200,CONVERT(date,'20200902'),CONVERT(time(0),'09:55:00'),'IN'),
                (200,CONVERT(date,'20200902'),CONVERT(time(0),'10:00:00'),'IN'),
                (200,CONVERT(date,'20200902'),CONVERT(time(0),'21:00:00'),'OUT'))V(CardID,[Date],[Time],TransactionCode)),
Changes AS(
    SELECT CardID,
           DATEADD(MINUTE,DATEDIFF(MINUTE, '00:00:00',[time]),CONVERT(datetime2(0),[date])) AS Dt2, --Way easier to work with later
           TransactionCode,
           CASE TransactionCode WHEN LEAD(TransactionCode) OVER (PARTITION BY CardID ORDER BY [Date],[Time]) THEN 0 ELSE 1 END AS CodeChange
    FROM VTE V),
Groups AS(
    SELECT CardID,
           dt2,
           TransactionCode,
           ISNULL(SUM(CodeChange) OVER (PARTITION BY CardID ORDER BY dt2 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS Grp
    FROM Changes),
MinMax AS(
    SELECT CardID,
           TransactionCode,
           CASE TransactionCode WHEN 'IN' THEN MIN(dt2) WHEN 'Out' THEN MAX(dt2) END AS GrpDt2
    FROM Groups
    GROUP BY CardID,
             TransactionCode,
             Grp),
--And now original Logic
CTE AS(
    SELECT CardID,
           GrpDt2 AS DatetimeIn,
           LEAD([GrpDt2]) OVER (PARTITION BY CardID ORDER BY GrpDt2) AS DateTimeOut,
           TransactionCode
    FROM MinMax)
SELECT C.CardID,
       CONVERT(date,DatetimeIn) AS DateIn,
       CONVERT(time(0),DatetimeIn) AS TimeIn,
       CONVERT(date,DatetimeOut) AS DateOtt,
       CONVERT(time(0),DatetimeOut) AS TimeOut,
       DATEADD(MINUTE, DATEDIFF(MINUTE,DatetimeIn, DateTimeOut), CONVERT(time(0),'00:00:00')) AS Duration
FROM CTE C
WHERE TransactionCode = 'IN';

推荐阅读