Reputation: 972
I'm trying to create a procedure that will:
102
100 to 103
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)
Upvotes: 3
Views: 165
Reputation: 18559
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
Upvotes: 0
Reputation: 82020
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
Upvotes: 6