By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable (
EDate date,
EDateID int,
RID int,
rNum int,
prev_rNum int,
seqRec int
);
insert into mytable values
('2023-12-03', 14425, 7507, 1, null, 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),
('2023-10-05', 13400, 7507, 11, 9, 0),
('2023-10-04', 13400, 7507, 12, 11, 1);
10 rows affected
WITH 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 *
FROM cte
| EDate | EDateID | RID | rNum | prev_rNum | seqRec | grp |
|---|---|---|---|---|---|---|
| 2023-12-03 | 14425 | 7507 | 1 | null | 1 | 0 |
| 2023-12-01 | 13422 | 7507 | 2 | 1 | 1 | 0 |
| 2023-11-19 | 13418 | 7507 | 3 | 2 | 1 | 0 |
| 2023-11-12 | 13413 | 7507 | 4 | 3 | 1 | 0 |
| 2023-11-11 | 13414 | 7507 | 5 | 4 | 1 | 0 |
| 2023-11-05 | 13412 | 7507 | 6 | 5 | 1 | 0 |
| 2023-10-14 | 13401 | 7507 | 8 | 6 | 0 | 1 |
| 2023-10-06 | 13400 | 7507 | 9 | 8 | 1 | 1 |
| 2023-10-05 | 13400 | 7507 | 11 | 9 | 0 | 2 |
| 2023-10-04 | 13400 | 7507 | 12 | 11 | 1 | 2 |
WITH 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
| Total |
|---|
| 6 |