Find consecutive sequence and suggest next number in sequence-CodePudding

I'm trying to create a procedure that will:

take any number as an input e.g. 102
find the sequence range it belongs e.g. 100 to 103
return a suggested next number to the user e.g. 104

The table itself will look something like this:

Num
100
101
102
103
110
111
112
113
114
115
120
121

Ideally the output of the query would return something like this:

start	end	nextNr
100	103	104
110	115	116
120	121	122

I this what I'm trying to do is linked to some kind of Gap and Island technique. I had a look at trying something from here but couldn't quite get it to work. Gaps and Islands Link

This is what I tried coming up with...

WITH cteSource(ID, Seq, Num)
AS(
    SELECT d.ID, f.Seq, f.Num
    FROM (
        SELECT 
            ID,
            ROW_NUMBER() OVER (PARTITION BY ID ORDER BY MIN(SeqNo)) AS Grp,
            MIN(SeqNo) AS StartSeqNo,
            MAX(SeqNo) AS EndSeqNo
        FROM
            (
            SELECT 1 ID, Num SeqNo, 
               Num - ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY Num) AS RN
            FROM 
            Number
            ) AS a
        GROUP BY ID, RN
        ) d
    CROSS APPLY (
            VALUES (d.Grp, d.EndSeqNo   1),(d.Grp -1, d.StartSeqNo -1)
            ) AS f(Seq, Num)
)
SELECT ID, MIN(Num) AS StartSeqNo, MAX(Num) AS EndSeqNo, MAX(Num)   1 as NextNr
FROM cteSource
GROUP BY ID, Seq
HAVING COUNT(*) = 2

The result looks like this:

StartSeqNo	EndSeqNo	NextNr
104	109	110
116	119	120

Here's the setup:

CREATE TABLE [dbo].[Number](
    [Num] [int] NULL
) 
GO

INSERT INTO Number
(Num)
VALUES
(100),
(101),
(102),
(103),
(110),
(111),
(112),
(113),
(114),
(115),
(120),
(121)

CodePudding user response：

Perhaps this will help.

Select [Start]  = min(num)
      ,[End]    = max(num)
      ,[NextNr] = max(num)   1
 From (
        Select * 
              ,Grp = num - row_number() over (order by num)
         From  number
      ) A
 Group By Grp

Results

Start   End     NextNr
100     103     104
110     115     116
120     121     122

CodePudding user response：

Your first subquery works fine to get groups and start and end number:

  SELECT 
            ROW_NUMBER() OVER (PARTITION BY ID ORDER BY MIN(SeqNo)) AS Grp,
            MIN(SeqNo) AS StartSeqNo,
            MAX(SeqNo) AS EndSeqNo
        FROM
            (
            SELECT 1 ID, Num SeqNo, 
               Num - ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY Num) AS RN
            FROM 
            Number
            ) AS a
        GROUP BY ID, RN

You just then needlessly complicated it instead of just using EndSeqNo 1 for NextNr:

WITH CTE_Groups AS 
(
        SELECT 
            ROW_NUMBER() OVER (PARTITION BY ID ORDER BY MIN(SeqNo)) AS Grp,
            MIN(SeqNo) AS StartSeqNo,
            MAX(SeqNo) AS EndSeqNo
        FROM
            (
            SELECT 1 ID, Num SeqNo, 
               Num - ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY Num) AS RN
            FROM 
            Number
            ) AS a
        GROUP BY ID, RN
)
SELECT *, EndSeqNo   1 AS NextNr 
FROM 
CTE_Groups

EDIT: and with further removing unnecessary partitons and extra columns

WITH CTE_Groups AS 
(
        SELECT 
            MIN(SeqNo) AS StartSeqNo,
            MAX(SeqNo) AS EndSeqNo
        FROM
            (
            SELECT  Num SeqNo, 
               Num - ROW_NUMBER() OVER (ORDER BY Num) AS RN
            FROM 
            Number
            ) AS a
        GROUP BY RN
)
SELECT *, EndSeqNo   1 AS NextNr 
FROM 
CTE_Groups