使用登录事件计算SQL Server中登录和注销时间的最小和最大 [英] Calculate min and max of login and logout time in SQL Server with login events

问题描述

我有呼叫中心座席的登录和登出表，这些座席的工作班次是从22到06，所以我需要计算的是loginTime的最小值和logOutTime的最大值，但是这变得很复杂，因为在某些情况下，某个代理在该时间段内断开连接，然后在另一个轮班开始的同一天在数据库中创建另一个登录事件，依此类推.

I have login and logout table of call center agents, these agents have a work shift from 22 to 06, so what I need to calculate is the min of loginTime and max of logOutTime, but this is where it becomes complex, because there are cases where an agent is disconnected during that time range, then another login event is created in the database on the same day where another shift begins and so on.

我尝试使用ROW_NUMBER函数以及将MIN和MAX与ROWS PRECEDING和FOLLOWING一起使用来管理结果，但是它不起作用，我的代码:

I've tried to manage results with ROW_NUMBER functions and using MIN and MAX with ROWS PRECEDING and FOLLOWING but it didn't work, my code:

SELECT  agentId
       ,position
       ,loginDate
       ,loginTime
       ,logoutDate
       ,logoutTime
       ,MIN((CAST(loginDate AS DATETIME) + CAST(loginTime AS DATETIME)))
             OVER(PARTITION BY agentId, position ORDER BY agentId, loginDate, loginTime) minLoginTime
       ,MAX((CAST(logoutDate AS DATETIME) + CAST(logoutTime AS DATETIME)))
       OVER(PARTITION BY agentId, position ORDER BY agentId, loginDate, loginTime ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING) maxLogoutTime
FROM @tbl
ORDER BY loginDate
       ,loginTime
       ,logoutDate
       ,logoutTime

这是表格示例:

DECLARE @tbl TABLE(agentId VARCHAR(10), position VARCHAR(10), loginDate DATE, loginTime TIME(0), logoutDate DATE, logoutTime TIME(0))

INSERT INTO @tbl SELECT '311338', '230025', '2019-06-03', '21:59:00', '2019-06-04', '06:00:00'
INSERT INTO @tbl SELECT '311338', '230025', '2019-06-04', '21:59:00', '2019-06-04', '23:30:00'
INSERT INTO @tbl SELECT '311338', '230025', '2019-06-04', '23:31:00', '2019-06-05', '06:01:00'
INSERT INTO @tbl SELECT '311338', '230038', '2019-06-05', '21:59:00', '2019-06-06', '02:13:00'
INSERT INTO @tbl SELECT '311338', '230038', '2019-06-06', '02:14:00', '2019-06-06', '06:00:00'
INSERT INTO @tbl SELECT '311338', '230037', '2019-06-06', '22:00:00', '2019-06-07', '06:00:00'
INSERT INTO @tbl SELECT '311338', '230038', '2019-06-07', '21:59:00', '2019-06-08', '00:53:00'
INSERT INTO @tbl SELECT '311338', '230038', '2019-06-08', '00:53:00', '2019-06-08', '06:00:00'
INSERT INTO @tbl SELECT '311338', '230038', '2019-06-09', '22:00:00', '2019-06-10', '06:09:00'

SELECT agentId
    ,position
    ,(CAST(loginDate AS DATETIME) + CAST(loginTime AS DATETIME)) loginTime
    ,(CAST(logoutDate AS DATETIME) + CAST(logoutTime AS DATETIME)) logoutTime
  FROM @tbl

agentId 311338的预期结果将是:

The expected result for agentId 311338 would be:

2019年5月6日:minLogin = 2019-06-05 21:59:00.000 maxLogout = 2019-06-06 06:00:00.000

Day 2019-05-06: minLogin = 2019-06-05 21:59:00.000 maxLogout = 2019-06-06 06:00:00.000

2019年6月6日:minLogin = 2019-06-06 22:00:00.000 maxLogout = 2019-06-07 06:00:00.000

Day 2019-06-06: minLogin = 2019-06-06 22:00:00.000 maxLogout = 2019-06-07 06:00:00.000

2019年6月7日:minLogin = 2019-06-07 21:59:00.000 maxLogout = 2019-06-08 06:00:00.000

Day 2019-06-07: minLogin = 2019-06-07 21:59:00.000 maxLogout = 2019-06-08 06:00:00.000

一天....

如您所见，没有时间可以浪费，如果同一天有多个登录事件，我必须检查它是否属于上一天班次或属于第二天班次.

As you can see no time must be discarded, if there's more than one login event on the same date, I have to check if it is part of the last day shift or part of the next day shift.

希望你们能帮助我.

推荐答案

只要人们在08:00之后不停止轮班，这将对您有用(为简单起见，我稍微更改了您的餐桌)

As long as people do not stop their shift after 08:00, this will work for you (I've changed your table slightly for simplicity)

DECLARE @tbl TABLE(agentId VARCHAR(10), loginMoment DATETIME, logoutMoment DATETIME)

INSERT INTO @tbl SELECT '311338', '2019-06-03 21:59:00', '2019-06-04 06:00:00'
INSERT INTO @tbl SELECT '311338', '2019-06-04 21:59:00', '2019-06-04 23:30:00'
INSERT INTO @tbl SELECT '311338', '2019-06-04 23:31:00', '2019-06-05 06:01:00'
INSERT INTO @tbl SELECT '311338', '2019-06-05 21:59:00', '2019-06-06 02:13:00'
INSERT INTO @tbl SELECT '311338', '2019-06-06 02:14:00', '2019-06-06 06:00:00'
INSERT INTO @tbl SELECT '311338', '2019-06-06 22:00:00', '2019-06-07 06:00:00'
INSERT INTO @tbl SELECT '311338', '2019-06-07 21:59:00', '2019-06-08 00:53:00'
INSERT INTO @tbl SELECT '311338', '2019-06-08 00:53:00', '2019-06-08 06:00:00'
INSERT INTO @tbl SELECT '311338', '2019-06-09 22:00:00', '2019-06-10 06:09:00'


SELECT agentId
    ,DATEADD(HOUR, 8, MIN(DATEADD(HOUR, -8, loginMoment)))
    ,DATEADD(HOUR, 8, MAX(DATEADD(HOUR, -8, logoutMoment)))
FROM @tbl
GROUP BY agentId, CAST(DATEADD(HOUR, -8, loginMoment) AS DATE)

结果如您所愿:

agentId login logout
311338  03/06/2019 21:59:00 04/06/2019 06:00:00
311338  04/06/2019 21:59:00 05/06/2019 06:01:00
311338  05/06/2019 21:59:00 06/06/2019 06:00:00
311338  06/06/2019 22:00:00 07/06/2019 06:00:00
311338  07/06/2019 21:59:00 08/06/2019 06:00:00
311338  09/06/2019 22:00:00 10/06/2019 06:09:00

这篇关于使用登录事件计算SQL Server中登录和注销时间的最小和最大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！