How can I sequentially count in SQL only when a criteria is met?

May 19, 2023

I have a table of "actions" that take place on work items. I was originally sequentially counting each action sequentially in the table using Row Number and Partition By which is working perfectly fine as below.

Current Table

+---------------+--------------------+-----------+-----------+--------------+
| Work Item Ref | Work Item DateTime | Auditable | Action ID | Action Count |
+---------------+--------------------+-----------+-----------+--------------+
|          2500 | 19/05/2023 10:01   | Yes       |        20 |            1 |
|          2501 | 19/05/2023 10:02   | Yes       |        11 |            1 |
|          2501 | 19/05/2023 10:03   | Yes       |         9 |            2 |
|          2501 | 19/05/2023 10:04   | No        |        19 |            3 |
|          2501 | 19/05/2023 10:06   | Yes       |         5 |            4 |
|          2502 | 19/05/2023 10:04   | No        |         2 |            1 |
|          2502 | 19/05/2023 10:05   | Yes       |         4 |            2 |
+---------------+--------------------+-----------+-----------+--------------+

Code

ROW_NUMBER() OVER(PARTITION BY [Work Item Ref] ORDER BY [Work Item DateTime] asc) AS [Action Count]

But now, we require the same count but only where the "Auditable" column is showing as ‘Yes’. I have tried adding a WHERE clause between PARTITION BY and ORDER BY but realise this isn’t the correct syntax. I need it to essentially only count sequentially, when the criteria is met. How can I achieve the below example?

Desired Results

+---------------+--------------------+-----------+-----------+--------------+
| Work Item Ref | Work Item DateTime | Auditable | Action ID | Action Count |
+---------------+--------------------+-----------+-----------+--------------+
|          2500 | 19/05/2023 10:01   | Yes       |        20 |            1 |
|          2501 | 19/05/2023 10:02   | Yes       |        11 |            1 |
|          2501 | 19/05/2023 10:03   | Yes       |         9 |            2 |
|          2501 | 19/05/2023 10:04   | No        |        19 |              |
|          2501 | 19/05/2023 10:06   | Yes       |         5 |            3 |
|          2502 | 19/05/2023 10:04   | No        |         2 |              |
|          2502 | 19/05/2023 10:05   | Yes       |         4 |            1 |
+---------------+--------------------+-----------+-----------+--------------+

>Solution :

You could just partition your row_number() by the additional column, and wrap it in a case expression:

CASE WHEN auditable = 'Yes' THEN
    ROW_NUMBER() OVER(
        PARTITION BY [Work Item Ref], [Auditable]
        ORDER BY [Work Item DateTime]
    ) 
END AS [Action Count]