How restart the incremenal from a temp table in SQL

I want to try to do an incremental number for a table, that has to return to 0 when seeing from the previous column (LAG) 0.

This is what I tried to do:

DROP TABLE IF EXISTS #a
DROP TABLE IF EXISTS #b
CREATE table #a (
    aa varchar(50),
    bb varchar(50)  )

    CREATE table #b (
    bb varchar(50)
        )
    INSERT INTO #a
    SELECT 'PR1','D10'
    UNION SELECT 'PR1','D20'
    UNION SELECT 'PR1','D30'
    UNION SELECT 'PR1','D40'
    UNION SELECT 'PR1','D80'
    UNION SELECT 'PR1','D90'
    UNION SELECT 'PR2','D50'
    UNION SELECT 'PR2','D60'
    UNION SELECT 'PR3','D70'

    INSERT INTO #b
     SELECT 'D30'
    UNION SELECT 'D60'

   ;WITH tablee (a,b,c,d)
   AS
   (
    SELECT #a.*, CASE WHEN #b.bb is NULL THEN 1 ELSE 0 END  xx
    --,RANK () OVER (PARTITION BY PR  ORDER bY PR)
    ,SUM (CASE WHEN #b.bb is NULL THEN 1 ELSE 0 END) OVER ( PARTITION BY aa ORDER BY aa,#a.bb) - CASE WHEN #b.bb is NULL THEN 1 ELSE 0 END   
    FROM #a
    left join #b
    on #a.bb=#b.bb
    )
    SELECT * FROM tablee

----------
a    b  c   d
PR1 D10 1   0
PR1 D20 1   1
PR1 D30 0   2
PR1 D40 1   2
PR1 D80 1   3
PR1 D90 1   4
PR2 D50 1   0
PR2 D60 0   1
PR3 D70 1   0
----------

And this is what I am expecting to get out:

----------
a    b  c   d
PR1 D10 1   0
PR1 D20 1   1
PR1 D30 0   2
PR1 D40 1   0
PR1 D80 1   1
PR1 D90 1   2
PR2 D50 1   0
PR2 D60 0   1
PR3 D70 1   0

I’m not familiar with the WITH common_table_expression is the right way to solve my problem?

Answer

You can use apply to fill blank value to next c :

with cte as (
     select a.aa, a.bb, b.bb as c
     from #a a left join
          #b b
          on b.bb = a.bb 
)
select c.aa, c.bb, 
      (case when c.c is null then 1 else 0 end) as c,
       row_number() over (partition by c.aa, ct.c order by c.aa) - 1 as d
from cte c outer apply
     ( select top (1) c1.*
       from cte c1
       where c1.aa = c.aa and c1.c is not null and c1.bb >= c.bb
       order by c1.c 
     ) ct
order by c.aa, c.bb;