SQL Server 2017

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

create table test 
(
  Id int identity(1,1) primary key,
  Client_No int,
  Start_Date date,
  Stop varchar(3)
)
insert into test 
(Client_No, Start_Date, Stop) values
  (1,'2018-01-01','No')
 ,(1,'2018-02-01','No')
 ,(1,'2018-03-01','No')
 ,(1,'2018-04-01','Yes')
 ,(1,'2018-05-01','No')
 ,(1,'2018-06-01','No')
 
 ,(2,'2018-02-01','No')
 ,(2,'2018-03-01','No')
 ,(2,'2018-04-01','Yes')
 ,(2,'2018-05-01','No')
 ,(2,'2018-06-01','Yes')
 
11 rows affected

select *
, row_number() over (partition by Client_no, Rnk order by start_date) as rn
from
(
  select *
  , sum(case when Stop = 'Yes' then 1 else 0 end) over (partition by Client_No order by start_date) rnk
  from test
) q
order by Client_No, start_date


Id
Client_No
Start_Date
Stop
rnk
rn


1
1
01/01/2018 00:00:00
No
0
1

2
1
01/02/2018 00:00:00
No
0
2

3
1
01/03/2018 00:00:00
No
0
3

4
1
01/04/2018 00:00:00
Yes
1
1

5
1
01/05/2018 00:00:00
No
1
2

6
1
01/06/2018 00:00:00
No
1
3

7
2
01/02/2018 00:00:00
No
0
1

8
2
01/03/2018 00:00:00
No
0
2

9
2
01/04/2018 00:00:00
Yes
1
1

10
2
01/05/2018 00:00:00
No
1
2

11
2
01/06/2018 00:00:00
Yes
2
1

Id	Client_No	Start_Date	Stop	rnk	rn
1	1	01/01/2018 00:00:00	No	0	1
2	1	01/02/2018 00:00:00	No	0	2
3	1	01/03/2018 00:00:00	No	0	3
4	1	01/04/2018 00:00:00	Yes	1	1
5	1	01/05/2018 00:00:00	No	1	2
6	1	01/06/2018 00:00:00	No	1	3
7	2	01/02/2018 00:00:00	No	0	1
8	2	01/03/2018 00:00:00	No	0	2
9	2	01/04/2018 00:00:00	Yes	1	1
10	2	01/05/2018 00:00:00	No	1	2
11	2	01/06/2018 00:00:00	Yes	2	1