Calculate min and max of login and logout time in

问题:

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.

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

This is the table example:

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

The expected result for agentId 311338 would be:

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

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

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

Day....

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.

Hope you guys can help me.

回答1:

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)

The result is as you like specified:

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

回答2:

As far as I can see, the 'day' you are interested in is 6 hours behind the normal day - so try GROUP BY a DATE 6 hours behind the DATE/TIME

to further explain, I'm taking the login time and subtracting 6 hours, then taking the date part to group by (notional date)

    SELECT  agentId, 
        CAST(DATEADD(hour,-6,CAST(loginDate AS DATETIME) + CAST(loginTime AS DATETIME)) AS DATE) NotionalDate
        ,min((CAST(loginDate AS DATETIME) + CAST(loginTime AS DATETIME))) minloginTime
        ,max((CAST(logoutDate AS DATETIME) + CAST(logoutTime AS DATETIME))) maxlogoutTime
    FROM @tbl 
        group by agentId, 
            CAST(DATEADD(hour,-6,CAST(loginDate AS DATETIME) + CAST(loginTime AS DATETIME)) AS DATE)