SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
	Jul 25 2020 11:26:55 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table entryExitData(
EmployeeNumber int,
entrytime datetime,
exittime datetime
)
declare @dt DATETIME = '2021-02-07 01:00:00'

declare @emp int = 1
while @emp<3
begin
declare @i int = 0
while @i <17
begin
    insert into entryExitData
    values(@emp,@dt, dateadd(minute,30,@dt))
    
    set @dt = dateadd(hour,1,@dt)
    set @i = @i + 1
end
set @emp = @emp+1
end
34 rows affected

select * from entryexitdata


EmployeeNumber
entrytime
exittime


1
2021-02-07 01:00:00.000
2021-02-07 01:30:00.000

1
2021-02-07 02:00:00.000
2021-02-07 02:30:00.000

1
2021-02-07 03:00:00.000
2021-02-07 03:30:00.000

1
2021-02-07 04:00:00.000
2021-02-07 04:30:00.000

1
2021-02-07 05:00:00.000
2021-02-07 05:30:00.000

1
2021-02-07 06:00:00.000
2021-02-07 06:30:00.000

1
2021-02-07 07:00:00.000
2021-02-07 07:30:00.000

1
2021-02-07 08:00:00.000
2021-02-07 08:30:00.000

1
2021-02-07 09:00:00.000
2021-02-07 09:30:00.000

1
2021-02-07 10:00:00.000
2021-02-07 10:30:00.000

1
2021-02-07 11:00:00.000
2021-02-07 11:30:00.000

1
2021-02-07 12:00:00.000
2021-02-07 12:30:00.000

1
2021-02-07 13:00:00.000
2021-02-07 13:30:00.000

1
2021-02-07 14:00:00.000
2021-02-07 14:30:00.000

1
2021-02-07 15:00:00.000
2021-02-07 15:30:00.000

1
2021-02-07 16:00:00.000
2021-02-07 16:30:00.000

1
2021-02-07 17:00:00.000
2021-02-07 17:30:00.000

2
2021-02-07 18:00:00.000
2021-02-07 18:30:00.000

2
2021-02-07 19:00:00.000
2021-02-07 19:30:00.000

2
2021-02-07 20:00:00.000
2021-02-07 20:30:00.000

2
2021-02-07 21:00:00.000
2021-02-07 21:30:00.000

2
2021-02-07 22:00:00.000
2021-02-07 22:30:00.000

2
2021-02-07 23:00:00.000
2021-02-07 23:30:00.000

2
2021-02-08 00:00:00.000
2021-02-08 00:30:00.000

2
2021-02-08 01:00:00.000
2021-02-08 01:30:00.000

2
2021-02-08 02:00:00.000
2021-02-08 02:30:00.000

2
2021-02-08 03:00:00.000
2021-02-08 03:30:00.000

2
2021-02-08 04:00:00.000
2021-02-08 04:30:00.000

2
2021-02-08 05:00:00.000
2021-02-08 05:30:00.000

2
2021-02-08 06:00:00.000
2021-02-08 06:30:00.000

2
2021-02-08 07:00:00.000
2021-02-08 07:30:00.000

2
2021-02-08 08:00:00.000
2021-02-08 08:30:00.000

2
2021-02-08 09:00:00.000
2021-02-08 09:30:00.000

2
2021-02-08 10:00:00.000
2021-02-08 10:30:00.000


with data as
(
select 
   employeenumber,
   entrytime,
   exittime,
   min(entrytime) over(partition by employeenumber order by entrytime) firstentry,
   lead(entrytime,1) over (partition by employeenumber order by entrytime) nextentry
from entryExitData
)
select distinct
employeenumber,
firstEntry,
max(exittime) over (partition by employeenumber)
from
data
where datediff(hour,firstentry,nextentry)<=14


employeenumber
firstEntry
(No column name)


1
2021-02-07 01:00:00.000
2021-02-07 14:30:00.000

2
2021-02-07 18:00:00.000
2021-02-08 07:30:00.000

EmployeeNumber	entrytime	exittime
1	2021-02-07 01:00:00.000	2021-02-07 01:30:00.000
1	2021-02-07 02:00:00.000	2021-02-07 02:30:00.000
1	2021-02-07 03:00:00.000	2021-02-07 03:30:00.000
1	2021-02-07 04:00:00.000	2021-02-07 04:30:00.000
1	2021-02-07 05:00:00.000	2021-02-07 05:30:00.000
1	2021-02-07 06:00:00.000	2021-02-07 06:30:00.000
1	2021-02-07 07:00:00.000	2021-02-07 07:30:00.000
1	2021-02-07 08:00:00.000	2021-02-07 08:30:00.000
1	2021-02-07 09:00:00.000	2021-02-07 09:30:00.000
1	2021-02-07 10:00:00.000	2021-02-07 10:30:00.000
1	2021-02-07 11:00:00.000	2021-02-07 11:30:00.000
1	2021-02-07 12:00:00.000	2021-02-07 12:30:00.000
1	2021-02-07 13:00:00.000	2021-02-07 13:30:00.000
1	2021-02-07 14:00:00.000	2021-02-07 14:30:00.000
1	2021-02-07 15:00:00.000	2021-02-07 15:30:00.000
1	2021-02-07 16:00:00.000	2021-02-07 16:30:00.000
1	2021-02-07 17:00:00.000	2021-02-07 17:30:00.000
2	2021-02-07 18:00:00.000	2021-02-07 18:30:00.000
2	2021-02-07 19:00:00.000	2021-02-07 19:30:00.000
2	2021-02-07 20:00:00.000	2021-02-07 20:30:00.000
2	2021-02-07 21:00:00.000	2021-02-07 21:30:00.000
2	2021-02-07 22:00:00.000	2021-02-07 22:30:00.000
2	2021-02-07 23:00:00.000	2021-02-07 23:30:00.000
2	2021-02-08 00:00:00.000	2021-02-08 00:30:00.000
2	2021-02-08 01:00:00.000	2021-02-08 01:30:00.000
2	2021-02-08 02:00:00.000	2021-02-08 02:30:00.000
2	2021-02-08 03:00:00.000	2021-02-08 03:30:00.000
2	2021-02-08 04:00:00.000	2021-02-08 04:30:00.000
2	2021-02-08 05:00:00.000	2021-02-08 05:30:00.000
2	2021-02-08 06:00:00.000	2021-02-08 06:30:00.000
2	2021-02-08 07:00:00.000	2021-02-08 07:30:00.000
2	2021-02-08 08:00:00.000	2021-02-08 08:30:00.000
2	2021-02-08 09:00:00.000	2021-02-08 09:30:00.000
2	2021-02-08 10:00:00.000	2021-02-08 10:30:00.000