SQL Server increment rank Partition By split groupings of duplicate values

Question

Given a table like the following:

Part	Batch	TransactionDate
1	1001	2022-06-10 13:25:00
1	1001	2022-06-11 14:00:00
1	1002	2022-06-12 07:25:00
1	1002	2022-06-12 08:25:00
1	1002	2022-06-12 09:25:00
1	1001	2022-06-13 10:21:00
2	1003	2022-06-10 11:20:00
2	1004	2022-06-11 12:20:00

How can I rank by part and batch while ensuring the date/time order of TransactionDate column?

The table should be ordered by Part followed by TransactionDate
When there is a new part, the rank should restart from 1
Each block of consecutive part/batch should receive the same rank
When a batch changes from row to row within the same part (even if batch is a previous one from some row above), the rank should be incremented by 1
the number of rows in each consecutive block of part/batch numbers can vary

So I should get the following table:

Part	Batch	TransactionDate	Rank
1	1001	2022-06-10 13:25:00	1
1	1001	2022-06-11 14:00:00	1
1	1002	2022-06-12 07:25:00	2
1	1002	2022-06-12 08:25:00	2
1	1002	2022-06-12 09:25:00	2
1	1001	2022-06-13 10:21:00	3
2	1003	2022-06-13 11:20:00	1
2	1004	2022-06-13 12:20:00	2

I was thinking of using dense rank in a partition such as the below:

DENSE_RANK() OVER (PARTITION BY Part, Batch ORDER BY TransactionDate)

However, the above combines all part and batch combinations into subsequent rows and then orders by transaction date. Therefore, it ranks all part/batch combinations (of 1 and 1001 respectively) as 1. But I need it to respect the transaction date order as in the table above.

Any help on a query to do the above would be appreciated.

EDIT: the number of rows in each consecutive block of part/batch numbers can vary.

user_12492890 · Answer

One approach could be:

creating a column that assigns 1 when the "Batch" current value is different from the "Batch" value lagged one row, 0, partitioned by "Part" otherwise
computing a running sum over this boolean value on the same partitions

Here's the full query:

WITH cte AS (
    SELECT *,
           CASE WHEN Batch 
                  <> LAG(Batch, 1) OVER(PARTITION BY Part ORDER BY TransactionDate)
                THEN 1 
                ELSE 0
                END AS new_batch
    FROM tab
)
SELECT Part, 
       Batch,
       TransactionDate,
       ExpectedRank,
       1+SUM(new_batch) OVER(PARTITION BY Part ORDER BY TransactionDate) AS rank
FROM cte

Check the demo here.

SQL Server increment rank Partition By split groupings of duplicate values

1 Answers