What you're looking for is called: gaps and islands issue.
See:
The SQL of Gaps and Islands in Sequences[
^]
Islands and Gaps in Sequential Numbers[
^]
[EDIT]
Test it:
DECLARE @tmp TABLE(ID INT IDENTITY(1,1), SerialNo INT)
INSERT INTO @tmp (SerialNo)
VALUES(101),(102),(103),(104),(105),
(116),(117),(115),(119),(120)
SELECT E.SerialNo- E.RowNo AS Grp, MIN(E.SerialNo) AS GrpStartsAt , MAX(E.SerialNo) AS GrpEndsAt
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY SerialNo) AS RowNo, SerialNo
FROM @tmp
) AS E
GROUP BY E.SerialNo- E.RowNo
Result:
Grp | GrpStartsAt | GrpEndsAt |
---|
100 | 101 | 105 |
109 | 115 | 117 |
110 | 119 | 120 |
For such small portion of data, it should work like a charm.
The basic idea is to return
SerialNo
sorted in ascending order, then to use
RowNo
together with
SerialNo
to create
Grp
.