question

Here is the solution.

Use lag and lad to flag and delete the records.

id emp_id schedule

1 1 2020-03-01 07:00:00.000

2 1 2020-03-01 09:00:00.000

3 1 2020-03-01 20:00:00.000

4 1 2020-03-04 06:00:00.000

5 1 2020-03-04 16:00:00.000

6 1 2020-03-04 17:00:00.000

7 1 2020-03-04 22:00:00.000

8 1 2020-03-04 22:00:00.000

9 1 2020-03-05 06:00:00.000

10 1 2020-03-07 06:00:00.000

11 1 2020-03-07 10:00:00.000

12 2 2020-03-04 22:00:00.000

13 2 2020-03-05 06:00:00.000

14 2 2020-03-07 06:00:00.000

15 2 2020-03-07 11:00:00.000

16 2 2020-03-05 22:00:00.000

17 2 2020-03-06 06:00:00.000

18 2 2020-03-07 06:00:00.000

19 2 2020-03-07 10:00:00.000

20 1 2020-03-01 09:00:00.000

21 3 2020-03-01 07:00:00.000

22 3 2020-03-01 09:00:00.000

23 3 2020-03-01 09:00:00.000

24 3 2020-03-01 11:00:00.000

25 3 2020-03-01 11:00:00.000

26 3 2020-03-01 14:00:00.000

27 4 2020-03-01 07:00:00.000

28 4 2020-03-01 15:00:00.000

--DROP TABLE #tmp

SELECT

/* This is for test */

s.emp_id, s.schedule, LAG(schedule) OVER (PARTITION BY emp_id ORDER BY schedule) AS previous_schedule,

/* This is the logic */

CASE

WHEN schedule = LAG(schedule) OVER (PARTITION BY emp_id ORDER BY schedule)

THEN 1

WHEN schedule = LEAD(schedule) OVER (PARTITION BY emp_id ORDER BY schedule)

THEN 1

ELSE 0 END AS match,

ROW_NUMBER() OVER ( PARTITION BY emp_id ORDER BY schedule ASC) AS seq

INTO #tmp

FROM

[dbo].[test_schedule] s

ORDER BY emp_id ASC, schedule ASC

--DROP TABLE #tmp_1

SELECT * INTO #tmp_1 FROM #tmp

WHERE match=0

ORDER BY 1,2 ASC

--TEST

--SELECT * FROM #tmp_1

SELECT

a.emp_id,

a.schedule AS Schedule_Start_Time,

b.schedule AS Schedule_End_Time

FROM #tmp_1 a

INNER JOIN

#tmp_1 b

ON a.emp_id = b.emp_id

AND a.seq = b.seq+1

ORDER BY 1,2 ASC