Total number of employees logged in

Question

I want to calculate the total number of employees logged in the system at a particular time and also the total work hour of each.

We have the login system which stores the data in the following data structure:

1.EmpId
2.Status
3.Created

The above data is stored in the following table:

EmpId   Status  Created
1   In  2019-10-23 12:00:00
1   Out 2019-10-23 12:45:45
2   In  2019-10-23 14:25:40
1   In  2019-10-23 18:45:45
2   Out 2019-10-23 20:50:40
2   In  2019-10-24 1:27:24
3   In  2019-10-24 2:45:45

The In is followed by Out and vice versa. And the employees work duration is spread across days, I mean the in and out could be across the days.

I need to implement the following:

How to calculate the number of employees logged in at a particular time say, "2019-10-23 14:12:45".

How to calculate the total work hours of all the employees since start?

What happens if someone forgets to log out one day? Two IN without an OUT between? — jarlh, Oct 24 '19 at 07:17
Your table structure should be like (EmpId,LoginDateTime,LogOutDateTime). So you can have valid login/logout data. — Kiya, Oct 24 '19 at 07:24
@jarlh - Consider logout as mandatory ,there has to be out. if there is no Out then employee is logged in. — saurabh095, Oct 24 '19 at 08:59

Sebastian Brosch · Accepted Answer · 2019-10-24T07:56:04.400

You can use this query to get the employees registered on a specific time:

SELECT * 
FROM (
  SELECT EmpID, Created AS [In], LEAD(Created) OVER (PARTITION BY EmpID ORDER BY Created ASC) AS [Out]
  FROM table_name
  WHERE Status = 'In'
) t WHERE '2019-10-23 14:12:45' BETWEEN [In] AND [Out]
  OR '2019-10-23 14:12:45' >= [In] AND [Out] IS NULL;

... and the following query to get the total work hours of each employee as TIME value:

SELECT EmpID, CONVERT(TIME(0), DATEADD(S, ISNULL(SUM(DATEDIFF(S, [In], [Out])), 0), 0), 108) AS work_hours 
FROM (
  SELECT EmpID, Created AS [In], LEAD(Created) OVER (PARTITION BY EmpID ORDER BY Created ASC) AS [Out]
  FROM table_name
  WHERE Status = 'In'
) t GROUP BY EmpID

demo on dbfiddle.uk

You can also define and use a CTE (common table expression) on the queries instead of the sub-select to get a flat table with login time and logout time as columns:

WITH Employees(EmpID, [In], [Out]) AS
(
  SELECT EmpID, Created AS [In], LEAD(Created) OVER (PARTITION BY EmpID ORDER BY Created ASC) AS [Out]
  FROM table_name
  WHERE Status = 'In'
)

score 0 · Answer 2 · answered Oct 24 '19 at 11:54

Assuming that ins and outs are interleaved, then you can use conditional aggregation and filtering:

select sum(case when status = 'in' then 1
                when status = 'out' then -1
           end) as employees_at_time
from t
where create <= '2019-10-23 14:12:45';

Total number of employees logged in

2 Answers2