SQL Server Latest number of sequential records

ckocjqey 于 12个月前发布在其他

关注(0)|答案(2)|浏览(117)

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

sql-server

来源：https://stackoverflow.com/questions/77618969/latest-number-of-sequential-records

2条答案

按热度按时间

hfyxw5xn1#

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

赞(0）回复(0）举报 12个月前

bksxznpy2#

It appears that you are trying to find the longest consecutive sequence. If that's the case, you have a gaps and islands problem, which can be resolved by calculating the difference between the row numbers and the running total to obtain groups of sequences.

WITH mytable AS (
          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
),
cte AS (
   SELECT *,
           ROW_NUMBER() OVER(ORDER BY EDate DESC) 
           - SUM(seqRec) OVER(ORDER BY EDate DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
   FROM mytable
)
SELECT top 1 COUNT(*) Total
FROM cte
GROUP BY grp
ORDER BY count(1) DESC

Demo here

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Latest number of sequential records

2条答案

相关问题

热门标签

最新问答