I am trying to figure out how to get Consecutive numbers for a table with people's information when they are absent but when the person is not absent for one month the count should start over.

Here is an example of what I am looking for.

| Prior Month Absent In a Row | Absent? | MonthDate | PersonId |
|              1              |  Yes    | 2022-01-01|    101   |
|              2              |  Yes    | 2022-02-01|    101   |
|              -              |   No    | 2022-03-01|    101   |
|              1              |  Yes    | 2022-04-01|    101   |
|              2              |  Yes    | 2022-05-01|    101   |
|              3              |  Yes    | 2022-06-01|    101   |
|              -              |   No    | 2022-01-01|    102   |
|              1              |  Yes    | 2022-02-01|    102   |
|              2              |  Yes    | 2022-03-01|    102   |
|              3              |  Yes    | 2022-04-01|    102   |
|              4              |  Yes    | 2022-05-01|    102   |
|              5              |  Yes    | 2022-06-01|    102   |

So basically for Each Person Id when they are Absent in a row it should count it but when the follow-up month is a No then it starts back at 1.

I used a row partion by function, but that did not work well.

Any suggestion or link would be much appreciated.

CodePudding user response：

select   case when grp is not null then row_number() over(partition by PersonId, grp order by MonthDate) end as prior_month_row
        ,Absent 
        ,MonthDate  
        ,PersonId
from
(
select   *
        ,case when Absent = 'Yes' then count(case when Absent = 'No' then 1 end) over(partition by PersonId order by MonthDate) end as grp
from     t
) t
order by PersonId, MonthDate

Fiddle