How to track 5 minute gaps in scans for warehouse employees?

Question

I am looking to create a query that counts how many times an employee is inactive for 5-minutes. Right now, I have thought about a CASE statement that will break down their scans into 12- segments (don't mind the conversion-- we have to change all of our takes to 4 hours behind because the server is in another timezone (it sucks)

case 
    when DATEPART(minute, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, th1.ACTIVITY_DATE_TIME), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) between '0' and '4' then 0
    when DATEPART(minute, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, th1.ACTIVITY_DATE_TIME), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) between '5' and '9' then 1
    when DATEPART(minute, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, th1.ACTIVITY_DATE_TIME), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) between '10' and '14' then 2
    when DATEPART(minute, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, th1.ACTIVITY_DATE_TIME), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) between '15' and '19' then 3
    when DATEPART(minute, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, th1.ACTIVITY_DATE_TIME), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) between '20' and '24' then 4
    when DATEPART(minute, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, th1.ACTIVITY_DATE_TIME), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) between '25' and '29' then 5
    when DATEPART(minute, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, th1.ACTIVITY_DATE_TIME), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) between '30' and '34' then 6
    when DATEPART(minute, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, th1.ACTIVITY_DATE_TIME), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) between '35' and '39' then 7
    when DATEPART(minute, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, th1.ACTIVITY_DATE_TIME), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) between '40' and '44' then 8
    when DATEPART(minute, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, th1.ACTIVITY_DATE_TIME), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) between '45' and '49' then 9
    when DATEPART(minute, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, th1.ACTIVITY_DATE_TIME), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) between '50' and '54' then 10
    when DATEPART(minute, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, th1.ACTIVITY_DATE_TIME), DATENAME(TzOffset, SYSDATETIMEOFFSET())))) between '55' and '59' then 11
    END)) 

I then took the distinct amount of numbers 0-11 and subtracted it from 12. I know this is not a perfect system because if someone has a scan at 7:01 and the next one at 7:09, it will not flag a 5-minute gap.

Does anyone know a better way of tracking gaps in transaction history?

Answer 1

LAG analytic function comes to your rescue.

Here a simple demonstration using this sample data

create table emp_pause 
(emp_no int,
 work_time date);

insert into emp_pause values(1, to_date('01012019 0810','ddmmyyyy hh24mi'));
insert into emp_pause values(1, to_date('01012019 0812','ddmmyyyy hh24mi'));
insert into emp_pause values(1, to_date('01012019 0817','ddmmyyyy hh24mi'));
insert into emp_pause values(1, to_date('01012019 0818','ddmmyyyy hh24mi'));
insert into emp_pause values(1, to_date('01012019 0830','ddmmyyyy hh24mi'));
insert into emp_pause values(2, to_date('01012019 1000','ddmmyyyy hh24mi'));
insert into emp_pause values(2, to_date('01012019 1004','ddmmyyyy hh24mi'));
insert into emp_pause values(2, to_date('01012019 1012','ddmmyyyy hh24mi'));
insert into emp_pause values(2, to_date('01012019 1029','ddmmyyyy hh24mi'));
insert into emp_pause values(2, to_date('01012019 1030','ddmmyyyy hh24mi'));
commit;

This query calculate the difference of the current timestamp with the value from the previous row of the same employee in minutes.

select emp_no, work_time,
trunc((work_time - lag(work_time) over (partition by emp_no order by work_time)) *24*60) interval_minutes
from emp_pause order by emp_no, work_time;

    EMP_NO WORK_TIME           INTERVAL_MINUTES
---------- ------------------- ----------------
         1 01.01.2019 08:10:00                  
         1 01.01.2019 08:12:00                2 
         1 01.01.2019 08:17:00                5 
         1 01.01.2019 08:18:00                1 
         1 01.01.2019 08:30:00               12 
         2 01.01.2019 10:00:00                  
         2 01.01.2019 10:04:00                4 
         2 01.01.2019 10:12:00                8 
         2 01.01.2019 10:29:00               17 
         2 01.01.2019 10:30:00                1 

It remains only to filter the intervals that are longer than the requested 5 minutes:

with emp as (
select emp_no, work_time,
trunc((work_time - lag(work_time) over (partition by emp_no order by work_time)) *24*60) interval_minutes
from emp_pause)
select * from emp 
where interval_minutes >= 5
order by 1,2;

    EMP_NO WORK_TIME           INTERVAL_MINUTES
---------- ------------------- ----------------
         1 01.01.2019 08:17:00                5 
         1 01.01.2019 08:30:00               12 
         2 01.01.2019 10:12:00                8 
         2 01.01.2019 10:29:00               17