sqlsql-servert-sqlsql-server-2019

Latest number of sequential records


I'm attempting to find the latest sequential number count from a record list. I have the records in order and I've allocated a rank to them, and have flagged if the record is to be classed as sequential or not.

My issue is that I can't seem to work out how to stop the sequential count when a zero is reached ... so my end result in this example should equal 6.

My sample data looks like this, which is derived from :

SELECT top 100 percent
EDate, EDateID, RID, rNum, prev_rNum,               
CASE WHEN 
            prev_rNum IS NULL
            OR prev_rNum = rNum - 1
            OR prev_rNum = rNum
        THEN 1 ELSE 0 END AS seqRec
FROM myTable
where RID = 7507
GROUP BY     EDate, EDateID, RID, rNum, prev_rNum
ORDER BY     EDate desc, EDateID desc
EDate | EDateID | RID | rNum | prev_rNum | seqRec

2023-12-03  14425   7507    1       1
2023-12-01  13422   7507    2   1   1
2023-11-19  13418   7507    3   2   1
2023-11-12  13413   7507    4   3   1
2023-11-11  13414   7507    5   4   1
2023-11-05  13412   7507    6   5   1
2023-10-14  13401   7507    8   6   0
2023-10-06  13400   7507    9   8   1

SQL Server 2019


Solution

  • Do you need something like this?

    We will only select the row with seqRec = 0 and among all the rows with seqRec = 0 for a RID, we will select the row with MIN(prev_rNum). This will give us latest sequential number count from a record list.

    SELECT RID, MIN(prev_rNum) latest_sequential_number_count 
    FROM (
    
    SELECT 
            EDate, EDateID, RID, rNum, prev_rNum,               
            CASE WHEN 
                        prev_rNum IS NULL
                        OR prev_rNum = rNum - 1
                        OR prev_rNum = rNum
                    THEN 1 ELSE 0 END AS seqRec
            FROM myTable
            -- where RID = 7507
            GROUP BY     EDate, EDateID, RID, rNum, prev_rNum
    ) sub 
    WHERE seqRec = 0
    GROUP BY RID