从数据指针中选择先进先出时间-不同的日期和空条件 [英] Select first in and last out time - different date and null condition - from data finger

问题描述

这是我的数据手指表 [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-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

条件

1. 假设员工将在同一天/第二天进出。


2. 假设有员工在同一天/第二天将有多个IN和OUT。因此需要先入后出。


3. Duration =（FirstInTime-LastOutTime）

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

The current result I get using the query:

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,'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'))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';

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

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

=====需要的结果======

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

如何获取Date IN和TimeIN的NULL值？在条件FIRST IN和LAST OUT中。请帮忙，谢谢您。

How to get the NULL Value For the Date IN and TimeIN? With the condition FIRST IN AND LAST OUT. Please help, thank you in advance.

推荐答案

这是一个悬而未决的问题。这是一种使用窗口函数的方法：

This is a gaps-and-islands problem. Here is an approach using window functions:

select card_id, 
    min(case when transaction_code = 'IN' then dt end) dt_in,
    max(case when transaction_code = 'OUT' then dt end) dt_out
from (
    select t.*,
        sum(case when transaction_code = 'IN' and (lag_transaction_code is null or lag_transaction_code <> 'IN') then 1 else 0 end)
            over(partition by card_id order by dt) grp
    from (
        select t.*,
            lag(transaction_code) over(partition by card_id order by dt) lag_transaction_code
        from (
            select t.*, cast(date as datetime) + cast(time as datetime) dt
            from vte t
        ) t
    ) t
) t
group by card_id, grp
order by card_id, dt_in

这个想法是要识别第一个 IN（使用 lag（）和一个窗口 sum（））并使用它来构建相邻rec的组奥兹。然后，我们可以使用条件聚合来检索每个范围的相应范围。

The idea is to identify the first "IN"s (using lag() and a window sum()) and to use that to build groups of adjacent records. Then we can use conditional aggregation to retrieve the corresponding bounds each range.

请注意，您不应将日期日期和时间分量存储在两个不同的列中-这会使事情变得更加复杂，因为没有明显的好处。我添加了另一层嵌套来生成正确的 datetime s。

Note that you should not be storing date date and time components in two different columns - this makes things more complicated, for no obvious benefit. I added another level of nesting to generate proper datetimes.

DB Fiddle上的演示 ：

Demo on DB Fiddle:

card_id | dt_in                   | dt_out                 
------: | :---------------------- | :----------------------
    100 | 2020-09-01 08:00:00.000 | 2020-09-01 17:10:00.000
    100 | 2020-09-02 07:00:00.000 | 2020-09-02 16:00:00.000
    200 | null                    | 2020-09-02 02:15:00.000
    200 | 2020-09-02 09:55:00.000 | null                   

这篇关于从数据指针中选择先进先出时间-不同的日期和空条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！