Get Users Attendance Entry And Exit In One Row Sql Server

I have a table with all entries for employees. I need to get all the working hours and the entry and exit time of the user in one record. The table is like this: How can I do that

Solution 1:

Assuming that the ins and outs line up (that is, are strictly interleaved), you can use lead() and some filtering:

select t.empId, convert(date, datetime) asdate, datetime as timein,
       next_datetime as timeout,
       datediff(minute, datetime, next_datetime) / 60.0as decimal_hours
from (select t.*,
             lead(datetime) over (partition by empid orderby datetime) as next_datetime
      from t
     ) t
where entrytype = 'IN';

Note that this formats the duration as decimal hours rather than as a time. That part does not seem relevant to the actual question and just complicates the query.

Solution 2:

This adds LEAD entrytype to make sure there is a corresponding OUT row. Also, it divides the date difference in minutes by 60.0 (added decimal)

select t.empId EmpID, cast(datetime asdate) [Day], datetime [Timein],  next_datetime [Timeout],
       datediff(mi, datetime, next_datetime)/60.0 TotalHours
from (select t.*,
             lead(datetime) over (partitionby empid orderby datetime) as next_datetime,
             lead(entrytype) over (partitionby empid orderby datetime) as next_entrytype
      from t
     ) t
where entrytype ='IN'and next_entrytype='Out';

Solution 3:

Using Row_number to identify IN and OUT related to which employee:

SELECT EMPID, CAST([DATEUPDT] ASDATE) ASDate,
MAX(CASEWHEN ENTRYTYPE ='IN'THENCAST([DATEUPDT] ASTIME) END) AS TIMEIN, 
MAX(CASEWHEN ENTRYTYPE ='OUT'THENCAST([DATEUPDT] ASTIME) END) AS TIMEOUT,
ABS(DATEDIFF(MINUTE, MAX(CASEWHEN ENTRYTYPE ='OUT'THENCAST([DATEUPDT] ASTIME) END), MAX(CASEWHEN ENTRYTYPE ='IN'THENCAST([DATEUPDT] ASTIME) END)))/60AS DURATION
FROM 
    (
    SELECT A.*, 
    ROW_NUMBER() OVER(PARTITIONBY EMPID, [ENTRYTYPE]  ORDERBY [DATEUPDT]) RN1
    FROM EMPLOYEE_LOG A 
    ) X
GROUPBY EMPID, RN1, CAST([DATEUPDT] ASDATE)
ORDERBY EMPID, RN1;

Solution 4:

You can also "sessionize" in SQL Server - by using OLAP queries: With a counter that is at 1 when a new session begins and at 0 otherwise

WITH
input(id,empid,dttime,entrytype) AS (
          SELECT1,125,CAST('2020-08-13 08:10:00.000'AS DATETIME),'IN'UNIONALLSELECT1,157,CAST('2020-08-13 08:01:00.000'AS DATETIME),'IN'UNIONALLSELECT1,125,CAST('2020-08-13 15:21:00.000'AS DATETIME),'OUT'UNIONALLSELECT1,125,CAST('2020-08-13 15:24:00.000'AS DATETIME),'IN'UNIONALLSELECT1,125,CAST('2020-08-13 17:24:00.000'AS DATETIME),'OUT'UNIONALLSELECT1,157,CAST('2020-08-13 15:01:00.000'AS DATETIME),'OUT'UNIONALLSELECT1,125,CAST('2020-08-14 08:10:00.000'AS DATETIME),'IN'UNIONALLSELECT1,157,CAST('2020-08-14 08:01:00.000'AS DATETIME),'IN'UNIONALLSELECT1,125,CAST('2020-08-14 15:21:00.000'AS DATETIME),'OUT'UNIONALLSELECT1,125,CAST('2020-08-14 15:24:00.000'AS DATETIME),'IN'UNIONALLSELECT1,125,CAST('2020-08-14 17:24:00.000'AS DATETIME),'OUT'UNIONALLSELECT1,157,CAST('2020-08-14 15:01:00.000'AS DATETIME),'OUT'
)
,
with_session AS (
  SELECT*
  , SUM(CASE entrytype WHEN'IN'THEN1ELSE0END) OVER(
      PARTITIONBY empid ORDERBY dttime
    ) AS sessid
  FROM input
)
SELECT
  id
, empid
, sessid
, CAST(MAX(CASE entrytype WHEN'IN'THEN dttime END) ASDATE) ASday
, CAST(MAX(CASE entrytype WHEN'IN'THEN dttime END) ASTIME) AS indtm 
, CAST(MAX(CASE entrytype WHEN'OUT'THEN dttime END) ASTIME) AS outdtm 
, CAST( 
    MAX(CASE entrytype WHEN'OUT'THEN dttime END) 
  -MAX(CASE entrytype WHEN'IN'THEN dttime END) 
  ASTIME
  ) AS totalhours
FROM with_session
GROUPBY 
  id
, empid
, sessid
ORDERBY
  id
, 4
, empid
, sessid
;
-- out  id | empid | sessid |    day     |  indtm   |  outdtm  | totalhours -- out ----+-------+--------+------------+----------+----------+-------------- out   1 |   125 |      1 | 2020-08-13 | 08:10:00 | 15:21:00 | 07:11:00-- out   1 |   125 |      2 | 2020-08-13 | 15:24:00 | 17:24:00 | 02:00:00-- out   1 |   157 |      1 | 2020-08-13 | 08:01:00 | 15:01:00 | 07:00:00-- out   1 |   125 |      3 | 2020-08-14 | 08:10:00 | 15:21:00 | 07:11:00-- out   1 |   125 |      4 | 2020-08-14 | 15:24:00 | 17:24:00 | 02:00:00-- out   1 |   157 |      2 | 2020-08-14 | 08:01:00 | 15:01:00 | 07:00:00