SQL Server Ranking - Ignoring Duplicate Row Values

Question

I'm hoping someone can point me in the right direction here. I have a unique requirement with regards to ranking records in a SQL Server table. The table looks like this...

This table shows a member of a health club and their various status changes (A = Active, C = Canceled). I want to rank these status changes in a unique way. I'd like to first A record to be 1 and then ignore the next 3 A records. The first C record would then be ranked 1 as well and the following A record would have a rank of 2. Again, I would want to ignore the following 3 A records before the next C record.

You're probably wondering why we are seeing multiple of the same statuses in a row with new to/from time periods. This is b/c this came from a larger data set where other columns were actually changed while statuses stayed the same. My end goal is to only bring forward the relevant (ranked) rows and match up the Active/Canceled rows based on rank. Then I would create an entirely new table/query to establish new From/To dates for the different periods.

Thanks for any advice offered!!

Answer 1

I think you are looking for DENSE_RANK

https://learn.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql

Answer 2

Is this what you're looking for?

WITH 
    cte1 AS (
        SELECT 
            td.memid, td.DateFrom, td.DateTo, td.Status,
            R1 = ROW_NUMBER() OVER (ORDER BY td.DateFrom),
            R2 = ROW_NUMBER() OVER (PARTITION BY td.Status ORDER BY td.DateFrom)
        FROM
            #TestData td
        ),
    cte2 AS (
        SELECT 
            c1.memid, c1.DateFrom, td.DateTo, c1.Status, c1.R1,
            R3 = ROW_NUMBER() OVER (PARTITION BY c1.R1 - c1.R2 ORDER BY c1.R1)
        FROM
            cte1 c1
        )
SELECT 
    c2.memid, c2.DateFrom, td.DateTo, c2.Status,
    DENSE_RANK() OVER (ORDER BY c2.R1 - c2.R3)
FROM
    cte2 c2;

Answer 3

Thanks for the input guys. I figured out a solution that worked for me. It involves first using the LAG() function on the status column. From there, I created a "RankingFlag" field. The flag value would be 1 if the status for the current row is NULL OR <=> to the LAG status (previous row). Next, I just performed a rank on all those RankingFlag=1 fields with a partition by status.