时间戳

首页 > 解决方案 > 处理 SQL 日期时间中缺少的登录/注销值

sql - 处理 SQL 日期时间中缺少的登录/注销值

问题描述

我的 Sql 查询：

CREATE TABLE TimeLog (
    [User] NVARCHAR(6),
    [Event] NVARCHAR(3),
    [Time] DATETIME
);
INSERT INTO TimeLog VALUES
(N'Jibran',N'IN','2015-04-15 00:31:00'),
(N'Jibran',N'IN','2015-04-16 20:10:00'),
(N'Jibran',N'IN','2015-04-21 14:59:00'),
(N'Jibran',N'OUT','2015-04-22 01:01:00'),
(N'Jibran',N'IN','2015-04-22 10:46:00'),
(N'Jibran',N'OUT','2015-04-23 00:58:00'),
(N'Jibran',N'IN','2015-04-23 14:50:00'),
(N'Jibran',N'OUT','2015-04-24 01:37:00'),
(N'Jibran',N'OUT','2015-04-25 01:01:00'),
(N'Jibran',N'OUT','2015-04-27 00:57:00'),
(N'Jibran',N'IN','2015-04-17 10:32:00'),
(N'Jibran',N'IN','2015-04-29 15:03:00'),
(N'Jibran',N'OUT','2015-05-01 00:44:00'),
(N'Jibran',N'OUT','2015-05-02 01:19:00'),
(N'Jibran',N'IN','2015-05-02 15:08:00'),
(N'Jibran',N'OUT','2015-05-03 01:08:00'),
(N'Jibran',N'IN','2015-05-03 15:06:00'),
(N'Jibran',N'OUT','2015-05-04 01:01:00'),
(N'Jibran',N'IN','2015-05-04 15:11:00'),
(N'Jibran',N'OUT','2015-05-05 01:08:00');

SELECT TOP (30) UserName, EventName, EventTime
From AttendanceEvents
Where UserName = 'Jibran'

结果是 ：

在此处输入图像描述

有没有办法处理每个没有 OUT 日期时间值的 IN 值的缺失值？

我一直在阅读一篇文章： https ://www.red-gate.com/simple-talk/sql/t-sql-programming/calculating-gaps-between-overlapping-time-intervals-in-sql/

但是中途没看懂。

如何获得 Datetime 的平均值以将其用于缺失值？

预期输出将具有每个 IN 的 OUT 值。

谢谢。

标签： sqltsqldatedatetimetime

解决方案

我想出了一个将缺失的行添加到表中的解决方案——对于缺失的in行和缺失的out行。
我所做的与您所要求的不同的一件事是缺失的行在其源行中完成了 8 小时的一天。

请注意，每次只能由一个用户使用。

所以这里是如何：

首先，我创建了一个公用表表达式，其中包含表中属于特定用户的所有行。在那个 cte 中，我使用lagandlead来获取下一个事件、上一个事件和下一个事件时间，以及一个 row_number 列。

然后，我使用了基于该 cte 的所有三个查询的联合——一个用于原始行，一个用于带有in事件的新行，一个用于带有事件的新行out。

该脚本基于您的示例数据，您可以在 rextester 上查看在线演示。

DECLARE @User nvarchar(6) = N'Jibran';

WITH CTE AS
(
    SELECT  [User],
            [Event],
            [Time],
            ROW_NUMBER() OVER(ORDER BY [Time]) + 0.0 As rn,
            LAG([Event]) OVER(ORDER BY [Time])  As PrevEvent,
            LEAD([Event]) OVER(ORDER BY [Time])  As NextEvent,
            LEAD([Time]) OVER(ORDER BY [Time]) As NextEventTime
    FROM TimeLog
    WHERE [User] = @User
)

SELECT  [User],
        'OUT' As [Event],
        DATEADD(HOUR, 8, [Time]) As [Time],
        rn + 0.5 As rn
FROM CTE
WHERE NextEvent = [Event]
AND [Event] = 'IN'

UNION ALL

SELECT  [User],
        'IN' As [Event],
        DATEADD(HOUR, -8, [NextEventTime]) As [Time],
        rn - 0.3 As rn
FROM CTE
WHERE PrevEvent = [Event]
AND [Event] = 'OUT'

UNION ALL
SELECT  [User],
        [Event],
        [Time],
        rn
FROM CTE

ORDER BY rn

结果：

User        Event   Time                    rn
Jibran      IN      15.04.2015 00:31:00     1,0
Jibran      OUT     15.04.2015 08:31:00     1,5
Jibran      IN      16.04.2015 20:10:00     2,0
Jibran      OUT     17.04.2015 04:10:00     2,5
Jibran      IN      17.04.2015 10:32:00     3,0
Jibran      OUT     17.04.2015 18:32:00     3,5
Jibran      IN      21.04.2015 14:59:00     4,0
Jibran      OUT     22.04.2015 01:01:00     5,0
Jibran      IN      22.04.2015 10:46:00     6,0
Jibran      OUT     23.04.2015 00:58:00     7,0
Jibran      IN      23.04.2015 14:50:00     8,0
Jibran      OUT     24.04.2015 01:37:00     9,0
Jibran      IN      26.04.2015 16:57:00     9,7
Jibran      OUT     25.04.2015 01:01:00     10,0
Jibran      IN      29.04.2015 07:03:00     10,7
Jibran      OUT     27.04.2015 00:57:00     11,0
Jibran      IN      29.04.2015 15:03:00     12,0
Jibran      OUT     01.05.2015 00:44:00     13,0
Jibran      IN      02.05.2015 07:08:00     13,7
Jibran      OUT     02.05.2015 01:19:00     14,0
Jibran      IN      02.05.2015 15:08:00     15,0
Jibran      OUT     03.05.2015 01:08:00     16,0
Jibran      IN      03.05.2015 15:06:00     17,0
Jibran      OUT     04.05.2015 01:01:00     18,0
Jibran      IN      04.05.2015 15:11:00     19,0
Jibran      OUT     05.05.2015 01:08:00     20,0

旁注：您可以根据 rn.all 查询的哪个部分添加了哪一行。

更新

按照我们在问题评论中的对话 - 为了获得不同列中的来龙去脉，我将 union all 查询包装在另一个 cte 中，并在此之上再放一个 cte 以获得 bigint 行号。然后它只是一个带有扭曲的条件聚合问题 - 使用交叉应用将偶数和奇数行号配对成组。

这是完整的脚本 - 当然，还有一个在线演示：

DECLARE @User nvarchar(6) = N'Jibran';

WITH CTE AS
(
    SELECT  [User],
            [Event],
            [Time],
            ROW_NUMBER() OVER(ORDER BY [Time]) + 0.0 As rn,
            LAG([Event]) OVER(ORDER BY [Time])  As PrevEvent,
            LEAD([Event]) OVER(ORDER BY [Time])  As NextEvent,
            LEAD([Time]) OVER(ORDER BY [Time]) As NextEventTime
    FROM TimeLog
    WHERE [User] = @User
), CTERows AS
(
    -- Added out rows
    SELECT  [User],
            'OUT' As [Event],
            DATEADD(HOUR, 8, [Time]) As [Time],
            rn + 0.5 As rn
    FROM CTE
    WHERE NextEvent = [Event]
    AND [Event] = 'IN'

    UNION ALL

    -- Added in rows
    SELECT  [User],
            'IN' As [Event],
            DATEADD(HOUR, -8, [NextEventTime]) As [Time],
            rn - 0.3 As rn
    FROM CTE
    WHERE PrevEvent = [Event]
    AND [Event] = 'OUT'

    UNION ALL

    -- Existing rows
    SELECT  [User],
            [Event],
            [Time],
            rn
    FROM CTE
), CTEIntNumberedRows AS
(
    SELECT  [User],
            [Event],
            [Time],
            ROW_NUMBER() OVER (ORDER BY rn) As rn
    FROM CteRows
)

SELECT  [User],
        MAX(CASE WHEN [Event] = 'IN' THEN [Time] END) As 'IN',
        MAX(CASE WHEN [Event] = 'OUT' THEN [Time] END) As 'OUT',
        Pairs
FROM CTEIntNumberedRows
CROSS APPLY 
(
    SELECT CASE WHEN rn % 2 = 0 THEN rn-1 ELSE rn END As Pairs
) x
GROUP BY [User], Pairs
ORDER BY Pairs

推荐阅读