I need to display employee login and logout hours for each session of the day and calculate Total Working hours. If the employee login and logout many times, each session and total should be displayed.

My table will be look like this

Here AccessType 1=IN 2=OUT OutType 1=LogOut 2=Session Out

I want the output like this

This is what I tried

select[EmpId],
[LocationId],
ShiftDate, 
SessionStartTime, 
SessionEndTime
, Total_Time = right(concat('0', Total_Time / 3600), 2)   ':'   right(concat('0', Total_Time % 3600 / 60), 2)  
from (
    select
       [EmpId],[LocationId], ShiftDate = cast(min(LogDate) as date)
        , SessionStartTime = min(LogDate)
        , SessionEndTime = max(LogDate)
        , Total_Time = sum(ss)
      
    from (
        select
            *, ss = datediff(ss, LogDate, lead(LogDate) over (partition by [EmpId], grp order by LogDate))
        from (
            select
                *, grp = sum(diff) over (partition by [EmpId] order by LogDate)
            from (
                select
                    *, diff = iif(datediff(mi, lag(LogDate) over (partition by [EmpId] order by LogDate), LogDate) > 300 and [AccessType] = 1, 1, 0)
                from
                    [tblEmployeeAttendance]  where cast(LogDate as date) >= '2022-05-25'  and cast(LogDate as date) <= '2022-05-26'
            ) t
        ) t
    ) t 
    group by [EmpId],[LocationId], grp) t

I got result like this

Here the problem is I get only min login and max logout for the day in one row. But I need each login and logout session of the user on the day and total for each.

I request somebody to help on this.

CodePudding user response：

You can do this with an outer apply.
I don't know what the OutType is used for, what the difference is with AccessType. You did not explain that or any other logic so I just assumed it should work on AccessType.
If that is not the case you can easy adapt the logic in the subquery below.

select e.EmpId,
       e.LocationID,
       e.logdate as SessionStart,
       d.logdate as SessionEnd,
       datediff(hour, e.logdate, d.logdate) as Hours
from   emp e
  outer apply ( select top 1 emp.logdate
                from   emp
                where  emp.empid = e.empid
                and    emp.accesstype = 2
                and    emp.logdate > e.logdate
                order by emp.logdate
              ) d
where  e.accesstype = 1

See the DBFiddle here

Result