Counting Process Repetitions in SQL using Partitioning

Question

FYI - I really wasn't sure what title to give this question. Any guidance is most welcome.

Below I have a "parent" rug (A12345) that is eventually cut into 2 or more rugs. These rugs need to go through rigorous washing and I need to know how many washings that rug went through between the "Parent" and "Child" rugs.

So in total, "A12345-678" had 4 washings before it was cut, and then 3 washings after when it became separate.

Using the sequence number, I have to be able to label the process as the 1st, 2nd, 3rd, etc... With the Process_Num column being the desired column I'm trying to achieve in SQL.

Rug_Num Sequence_Num    Process Process_Num
A12345  10              Wash    1st Wash
A12345  11              Wash    2nd Wash
A12345  30              Wash    4th Wash
A12345  20              Wash    3rd Wash

 '----Rug cut into 2 child rugs'

A12345-678  40          Wash    5th Wash
A12345-678  50          Wash    6th Wash
A12345-678  60          Wash    7th Wash

A12345-997  40          Wash    5th Wash
A12345-997  50          Wash    6th Wash
A12345-997  60          Wash    7th Wash
A12345-997  70          Wash    8th Wash

Here is my "in-progress" statement but I can't figure out how to add something after "THEN" that will cause it to sequentially number the current sequence as "1st, 2nd, 3rd". My mind says to put another COUNT CASE WHEN after the 1st THEN but it will then be an infinite statement.

WITH toupdate AS (
    SELECT m.*,
        count(CASE WHEN (m.Process = 'Wash' 
                        AND m.Rug_Num = m.Rug_Num
                        AND m.sequence_num < m.sequence_num)
                        THEN  1 ELSE 0 END)
         OVER (PARTITION BY m.Process, m.Rug_Num, m.Process_Num) AS Process_Num_New
         FROM mfng_data m
        )
    UPDATE toupdate
        SET Process_Num = ISNULL(Process_Num_New,0);

So above I was trying to replicate a COUNTIFS (Excel) in SQL. The Excel example is below. The formula in Column H is:

=(COUNTIFS($C$3:$C$14,C3,$D$3:$D$14,"<"&D3,$E$3:$E$14,"Wash")+1)+IF(LEN(C3)>6,COUNTIFS($C$3:$C$14,$C$3,$E$3:$E$14,"Wash"),0)

I add the +1 so it will count how many were less than the current sequence and add 1 for the current sequence.

Answer 1

you can use count(*) with window function over()

there are 2 count() in the query below. The first one count the wash no for parent, and the 2nd count() is on the child

select  *, 
        Wash_No = count (case when Parent_Rug = Rug_Num then 1 end) 
                   over (partition by Parent_Rug order by Sequence_num) 
                + count (case when Parent_Rug <> Rug_Num then 1 end)  
                   over (partition by Rug_Num order by Sequence_num)

from    mfng_data