Row_number skip values

Question

I have a table like this:

The idea were to count only when I have "Include" at column include_appt, when it finds NULL, it should skip set is as "NULL" or "0" and on next found "Include" back to counting where it stopped.

The screenshot above I was almost able to do it but unfortunately the count didn't reset on next value.

PS: I can't use over partition because I have to keep the order by id ASC

Answer 1

I suggest using the DENSE_RANK() with the columns you have hidden (--*,):

SELECT
    row_num AS id,
    include_appt,
    CASE WHEN include_appt is not null
    THEN ROW_NUMBER() OVER(ORDER BY (SELECT 0)) 
        + 1 
        - DENSE_RANK() OVER(
            PARTITION BY /*some hidden columns*/ 
            ORDER BY/*some hidden columns*/) 
    ELSE NULL
    END AS row_num2
FROM C
ORDER BY row_num

Then the result will be: enter image description here