SQL Server 2017

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

CREATE TABLE da
([DTime] datetime, [NCode] varchar(10));

INSERT INTO da ([DTime], [NCode]) VALUES
('2020/05/07 08:44:08', 'A'),
('2020/05/07 08:44:53', 'A'),
('2020/05/07 08:45:38', 'B'),
('2020/05/07 08:46:23', 'B'),
('2020/05/07 08:47:08', 'B'),
('2020/05/07 08:47:53', 'A'),
('2020/05/07 08:48:38', 'A'),
('2020/05/07 08:49:23', 'C');

SELECT *
FROM da;


DTime
NCode


2020-05-07 08:44:08.000
A

2020-05-07 08:44:53.000
A

2020-05-07 08:45:38.000
B

2020-05-07 08:46:23.000
B

2020-05-07 08:47:08.000
B

2020-05-07 08:47:53.000
A

2020-05-07 08:48:38.000
A

2020-05-07 08:49:23.000
C


SELECT M.DTime,M.NCode,1 + SUM(M.Num) OVER (ORDER BY M.DTime) AS No
FROM (
SELECT da.*,
CASE WHEN NCode = LAG(NCode,1,NCode) OVER (ORDER BY DTime) THEN 0 ELSE 1 END AS Num
FROM da) M


DTime
NCode
No


2020-05-07 08:44:08.000
A
1

2020-05-07 08:44:53.000
A
1

2020-05-07 08:45:38.000
B
2

2020-05-07 08:46:23.000
B
2

2020-05-07 08:47:08.000
B
2

2020-05-07 08:47:53.000
A
3

2020-05-07 08:48:38.000
A
3

2020-05-07 08:49:23.000
C
4


SELECT PP.No,PP.NCode,MIN(DTime) AS minTime,MAX(DTime) AS MaxTime
FROM (
SELECT M.DTime,M.NCode,1 + SUM(M.Num) OVER (ORDER BY M.DTime) AS No
FROM (
SELECT da.*,
CASE WHEN NCode = LAG(NCode,1,NCode) OVER (ORDER BY DTime) THEN 0 ELSE 1 END AS Num
FROM da) M
) AS PP
GROUP BY PP.No,PP.NCode
ORDER BY PP.No,PP.NCode


No
NCode
minTime
MaxTime


1
A
2020-05-07 08:44:08.000
2020-05-07 08:44:53.000

2
B
2020-05-07 08:45:38.000
2020-05-07 08:47:08.000

3
A
2020-05-07 08:47:53.000
2020-05-07 08:48:38.000

4
C
2020-05-07 08:49:23.000
2020-05-07 08:49:23.000

DTime	NCode
2020-05-07 08:44:08.000	A
2020-05-07 08:44:53.000	A
2020-05-07 08:45:38.000	B
2020-05-07 08:46:23.000	B
2020-05-07 08:47:08.000	B
2020-05-07 08:47:53.000	A
2020-05-07 08:48:38.000	A
2020-05-07 08:49:23.000	C