Find consecutive sequence and suggest next number in sequence

user_2246365 0
Viewed 67

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)
sql-server sql
2 Answers

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
user_15700000

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
user_6121810
Related