问题描述
这是我的数据手指表 [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
条件
- 假设员工将在同一天/第二天进出。
- 假设有员工在同一天/第二天将有多个IN和OUT。因此需要先入后出。
- 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.
：
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
这篇关于从数据指针中选择先进先出时间-不同的日期和空条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！