How to increment a column like 1, 1, 2, 2, 3, 3 in SQL Server

This seems to work. I've copied your desired output as my input sample data just to show that my new column matches your Desired one, and also a couple of other renames for keywords:

declare @t table (ID int, DateOf datetime, EventNo int, DesiredFlow varchar(7))
insert into @t(ID,DateOf,EventNo,DesiredFlow) values
(4  ,'2022-02-07T00:00:00.000',1    ,'F1'),
(44 ,'2022-02-08T00:00:00.000',10   ,'F1'),
(47 ,'2022-02-09T00:00:00.000',1    ,'F2'),
(72 ,'2022-02-10T00:00:00.000',10   ,'F2'),
(75 ,'2022-02-10T00:00:00.000',1    ,'F3'),
(97 ,'2022-02-11T00:00:00.000',10   ,'F3'),
(100    ,'2022-02-11T00:00:00.000',1    ,NULL),
(113    ,'2022-02-28T00:00:00.000',1    ,'F4'),
(217    ,'2022-03-04T00:00:00.000',10   ,'F4'),
(235    ,'2022-03-22T00:00:00.000',10   ,NULL),
(241    ,'2022-03-22T00:00:00.000',1    ,'F5'),
(270    ,'2022-03-24T00:00:00.000',10   ,'F5'),
(273    ,'2022-03-24T00:00:00.000',1    ,'F6'),
(308    ,'2022-03-25T00:00:00.000',10   ,'F6')

;With Neighbours as (
    select
        *,
        LEAD(EventNo,1,NULL) OVER (ORDER BY ID) as Successor,
        LAG(EventNo,1,NULL) OVER (ORDER BY ID) as Predecessor
    from
        @t t
), NoStragglers as (
    select
        *
    from
        Neighbours
    where
        (EventNo = 1 and Successor = 10) or
        (EventNo = 10 and Predecessor = 1)
), Ordered as (
    select
        *, ROW_NUMBER() OVER (PARTITION BY EventNo ORDER BY ID) as rn
    from NoStragglers
)
select
    t.*,'F' + (CONVERT(varchar(10),o.rn)) as ActualFlow
from
    @t t
        left join
    Ordered o
        on
            t.Id = o.Id

Hopefully you can see how each CTE builds from the previous one (either by inspection or changing the final select to pull one one of the earlier CTEs instead).

Result:

ID          DateOf                  EventNo     DesiredFlow ActualFlow
----------- ----------------------- ----------- ----------- -----------
4           2022-02-07 00:00:00.000 1           F1          F1
44          2022-02-08 00:00:00.000 10          F1          F1
47          2022-02-09 00:00:00.000 1           F2          F2
72          2022-02-10 00:00:00.000 10          F2          F2
75          2022-02-10 00:00:00.000 1           F3          F3
97          2022-02-11 00:00:00.000 10          F3          F3
100         2022-02-11 00:00:00.000 1           NULL        NULL
113         2022-02-28 00:00:00.000 1           F4          F4
217         2022-03-04 00:00:00.000 10          F4          F4
235         2022-03-22 00:00:00.000 10          NULL        NULL
241         2022-03-22 00:00:00.000 1           F5          F5
270         2022-03-24 00:00:00.000 10          F5          F5
273         2022-03-24 00:00:00.000 1           F6          F6
308         2022-03-25 00:00:00.000 10          F6          F6

Which seems to match.

Check this,

create table #temp(id int,dates datetime,Events int)

insert into #temp(id,dates,events) values
(4      ,'2022-02-07 00:00:00.000', 1   )
,(44    ,'2022-02-08 00:00:00.000', 10  )
,(47    ,'2022-02-09 00:00:00.000', 1   )
,(72    ,'2022-02-10 00:00:00.000', 10  )
,(75    ,'2022-02-10 00:00:00.000', 1   )
,(97    ,'2022-02-11 00:00:00.000', 10  )
,(100   ,'2022-02-11 00:00:00.000', 1   )
,(113   ,'2022-02-28 00:00:00.000', 1   )
,(217   ,'2022-03-04 00:00:00.000', 10  )
,(235   ,'2022-03-22 00:00:00.000', 10  )
,(241   ,'2022-03-22 00:00:00.000', 1   )
,(270   ,'2022-03-24 00:00:00.000', 10  )
,(273   ,'2022-03-24 00:00:00.000', 1   )
,(308   ,'2022-03-25 00:00:00.000', 10  )

WITH CTE
     AS (SELECT Id, 
                Dates, 
                Events,
                CASE
                    WHEN Events = 1
                         AND LEAD(Events) OVER(
                             ORDER BY ID) = 10
                    THEN id
                    WHEN Events = 10
                         AND LAG(Events) OVER(
                             ORDER BY ID) = 1
                    THEN LAG(id) OVER(
                ORDER BY ID)
                    ELSE NULL
                END AS Flow
         FROM #temp),
     CtE1
     AS (SELECT *, 
                DENSE_RANK() OVER(
                ORDER BY flow) rn
         FROM CTe
         WHERE flow IS NOT NULL
         UNION ALL
         SELECT *, 
                NULL
         FROM CTE
         WHERE flow IS NULL)
     SELECT id, 
            dates, 
            events,
            CASE
                WHEN rn IS NOT NULL
                THEN concat('F', rn)
                ELSE NULL
            END Flow
     FROM cte1
     ORDER BY id;

drop table #temp

You can use cascading subqueries to assign a ROW_NUMBER() based on Flow values that are NOT NULL:

SELECT d.Id, 
       d.Date, 
       d.Event,
       CASE WHEN b.rn IS NULL THEN NULL 
            ELSE CONCAT('F', b.rn) 
       END AS Flow
FROM (SELECT 
        Id, 
        Date, 
        Event, 
        Flow,
        ROW_NUMBER() OVER (PARTITION BY Event ORDER BY Id ASC) as rn
   FROM (SELECT 
           Id, 
           Date, 
           Event,
           CASE WHEN Event = 1 AND LEAD(Event) OVER (ORDER BY Id) = 10 THEN 1
                WHEN Event = 10 AND LAG(Event) OVER (ORDER BY Id) = 1 THEN 1
                ELSE 0
            END as Flow
         FROM SystemEventsDiary WHERE Event IN (1, 10)
         ) a WHERE Flow = 1) b 
RIGHT JOIN SystemEventsDiary d ON b.id = d.Id
ORDER BY d.Id ASC

Result:

db<>fiddle here.