clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799290 fiddles created (41681 in the last week).

CREATE TABLE AAA ( [Level] int, [Check] varchar(10) ); INSERT INTO AAA([Level], [Check]) VALUES(0, 'N'), (1, 'Y'), (2, 'Y'), (1, 'N'), (1, 'N'), (1, 'N'), (2, 'N'), (2, 'N'), (2, 'N'), (2, 'N'), (2, 'N')
11 rows affected
 hidden batch(es)


SELECT [Level], [Check], Cnt = CASE WHEN [Level] = 1 AND LEAD([Level]) OVER(ORDER BY [Level]) =2 THEN (SELECT COUNT([Level]) FROM AAA WHERE [Level]=2 AND [Check] = 'N') ELSE NULL END FROM AAA a
Level Check Cnt
0 N
1 Y
1 N
1 N
1 N 5
2 N
2 N
2 N
2 N
2 N
2 Y
 hidden batch(es)