How to In Time and Out Time for the Employees whose Shifts are Day Switch?

Hello Muhammad,

Can you check the following script?

/*
create table employeedailyattendance (
        EmployeeID smallint,
        InDate date,
        EntryType char(1),
        InTime datetime,
        OutTime datetime
)
 
insert into employeedailyattendance select 1, '20140102', 'I', getdate(), NULL
insert into employeedailyattendance select 1, '20140103', 'O', NULL, dateadd(hh,10,getdate())

insert into employeedailyattendance select 3, '20140102', 'I', getdate(), NULL
insert into employeedailyattendance select 3, '20140103', 'O', NULL, dateadd(hh,5,getdate())

insert into employeedailyattendance select 1, '20140103', 'I', '20140103 16:00', NULL
insert into employeedailyattendance select 1, '20140103', 'O', NULL, '20140103 22:00'

*/
with cte as (
        SELECT 
                *,
                rn = ROW_NUMBER() over (partition by EmployeeID Order By INDate, ISNULL(InTime, OutTime))
        FROM employeedailyattendance 
        WHERE 
                InDate between '01/01/2014' and '01/31/2014' 
), cte2 as (
        select
                *,
                (rn + 1 )/ 2 d
        from cte
)
select 
        EmployeeID, 
        Min(InDate) InDate, MIN(InTime) as I, MAX(OutTime) as O,
        DATEDIFF(mi, MIN(InTime), MAX(OutTime)) period_mi
from cte2
group by EmployeeID,d 

I'm using multiple SQL CTE query

The first one numbers actions created by the employee using ROW_NUMBER() function

The second one pairs them by dividing the row number by 2

The last SELECT displays pairs and calculates period when the employee is inside the building

I hope that leads to a solution,

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials