I have a SQL Table with employees and a timestamp for their every action. How to calculate how much they worked, and how much they idled (20min gaps)?

Question

I have a very simple query that looks like this

SELECT
    Username
    ,FORMAT(Timestamp, 'MM/dd/yyyy') as Date
    ,FORMAT(Timestamp, 'hh:mm tt') as Time
FROM 
    AuditRecords

ORDER BY
    date desc
    ,Username
    ,Timestamp

Here is a sample output of running that query.

Username	Date	Time
Employee1	05/10/2022	12:10 AM
Employee1	05/10/2022	12:23 AM
Employee1	05/10/2022	12:25 AM
Employee1	05/10/2022	12:27 AM
Employee1	05/10/2022	12:28 AM
Employee1	05/10/2022	12:31 AM
Employee1	05/10/2022	12:33 AM
Employee1	05/10/2022	01:43 AM
Employee1	05/10/2022	02:18 AM
Employee1	05/10/2022	03:06 AM
Employee1	05/10/2022	03:20 AM
Employee1	05/10/2022	03:26 AM
Employee1	05/10/2022	04:02 AM
Employee1	05/10/2022	04:05 AM
Employee1	05/10/2022	04:07 AM
Employee1	05/10/2022	04:09 AM
Employee1	05/10/2022	04:14 AM
Employee2	05/10/2022	01:08 AM
Employee2	05/10/2022	01:13 AM
Employee2	05/10/2022	01:15 AM
Employee2	05/10/2022	01:22 AM
Employee2	05/10/2022	01:24 AM
Employee2	05/10/2022	01:28 AM
Employee2	05/10/2022	01:30 AM
Employee2	05/10/2022	01:32 AM
Employee2	05/10/2022	01:57 AM
Employee2	05/10/2022	01:58 AM
Employee2	05/10/2022	04:53 AM
Employee3	05/10/2022	12:50 AM
Employee3	05/10/2022	12:50 AM
Employee3	05/10/2022	12:50 AM
Employee3	05/10/2022	03:00 AM
Employee3	05/10/2022	03:58 AM
Employee3	05/10/2022	04:56 AM
Employee3	05/10/2022	05:42 AM

Now as you can see, there are three employees. Employee1 did their first task at 12:10 AM and their last task at 04:14 AM

Originally, what I was doing was taking their first and last action, and just subtracting them to see how many hours they worked.

SELECT
    Username
    ,FORMAT(Timestamp, 'MM/dd/yyyy') as Date
    ,FORMAT (min(Timestamp) , 'hh:mm tt') as FirstAction
    ,FORMAT (max(Timestamp) , 'hh:mm tt') as LastAction 
    ,DATEDIFF(hour, min(Timestamp),max(Timestamp)) AS HoursWorked
FROM 
    AuditRecords
GROUP BY
    Username
    ,FORMAT(Timestamp, 'MM/dd/yyyy')
ORDER BY
    date desc
    ,Username

So for the case of Employee1, it would return 4 hours. As the difference between 12AM and 4 AM.

However, now we want to calculate this metric in a more sophisticated way. For example, around the middle of his shift, his actions took place at

01:43 AM, 02:18 AM, 03:06 AM

Which is roughly 40 minutes between each action. So instead of counting this as working time, I actually want to count this as idle time.

In the beginning of Employee1's shift, he was doing actions every 2-3 minutes. So I want to count this as working time.

However, starting at 12:33 AM to 3:06 AM, since his actions are so spread apart, I want to count this as idle time.

I want to be able to set a custom idle metric. Like 30 or 40 minutes. And if their consecutive actions have more than that metric as a gap, then we count it as being idle.

Is this possible to do with a SQL query? I would have to somehow check each row compared to it's previous one.

So in the end, I want to return a total of working minutes, and a total of idle minutes, per day per employee.

Any help or guidance in the right direction would be greatly appreciated.

Answer 1

You want the lag function. Here is a basic example with it... from here you can put a simple case statement (or iif since its T-SQL) on the lower query (t1) to see if you want to count it as an idle time event or not and then just sum that up in the upper query. I should also mention that with lag function you are likely to have a null record at each grouping (in this case, its per day per employee) so you will probably want to omit those... I didn't in my example so you can have the option. If you comment out the upper query (t2) you can run just t1 alone and see what it is doing.

select
  *
  , totalhours-hoursworked as IdleTime
from(
select
  username
  ,dateval
  , sum(isnull(EntryMinutes,0)) / 60 as HoursWorked
  , cast(datediff(second, min(timeval), max(timeval)) / 60 / 60 as decimal(38,20)) as TotalHours
from( 
select
  *
  , EntryMinutes = cast(datediff( second,
    timeval, lag(timeval, 1) over(partition by username, dateval order by username, dateval desc, timeval desc)) / 60 as decimal(38,20))
  from AuditRecords
) as t1
group by username, dateval
) as t2

this should get you started