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 |
+---------------+--------------------+-----------+-----------+--------------+
1条答案
按热度按时间6jygbczu1#
You could just partition your
row_number()by the additional column, and wrap it in acaseexpression: