# Generate all the continous intervals from a series

Viewd192

I have this problem I can't quite solve. I can get the missing numbers interval, but I can't piece them back together to from my continous series.

So if I have a series defined as [1000,1001,1002,1003,1005,1006,1008] I want to extract the three continuous series [1000,1001,1002,1003] and [1005,1006] and [1008]. Using a simple CTE I got 1003, 1005,1006 and 1008, so I'm able to get the end and start of the intervals, but what now?

In the end I want a table that looks like this:

``````|to   |from  |
|1000 |1003  |
|1005 |1006  |
|1008 |1008  |
``````

Anyone got a smart solution they want to share?

EDIT: Here is the (probably reduntant) CTE:

``````WITH MissingNumbers (FromNumber, ToNumber) AS
(
SELECT
T1.TaxLabelNumber,
T2.TaxLabelNumber
FROM TaxLabel T1
JOIN TaxLabel T2
ON T1.TaxLabelId + 1 = T2.TaxLabelId
WHERE T1.TaxLabelNumber <> T2.TaxLabelNumber - 1
)
SELECT * INTO #TempNumbers
FROM MissingNumbers
``````

EDIT2: Ofc. there was a change of plans, so I no longer need this kind of solution. Thank you for all the replies though! Very helpful :D

192

## 3 ответов

Самый простой выход - создать таблицу с TaxLabelNumbers, чтобы можно было выполнить внешнее соединение.

Также можно создать такую ​​таблицу в CTE, но это не очень эффективно.

``` ```with TaxLabelSeq( Number ) as
(
select @FromNumber as Number
union all
select Number + 1
from NumberSequence
where Number < @ToNumber
)
``` ```

CTE разумно по умолчанию составляет 100 рекурсий, поэтому вам нужно увеличить это, если вам нужно более 100 чисел:

``` ```select * from TaxLabelSeq option (MaxRecursion 4711)
``` ```

Попробуй

``` ```SELECT SSTART.num series_start, MIN(SEND.num) series_end
FROM   #series SSTART, #series SEND
WHERE
/* anything that does not have a predecessor is a START */
SSTART.num - 1 NOT IN (SELECT num FROM #series) AND
/* anything that does not have a following entry is an END */
SEND.num + 1 NOT IN (SELECT num FROM #series)   AND
SEND.num >= SSTART.num
/* we group over each START, so we can get the corresponding END with MIN */
GROUP BY SSTART.num
``` ```
``````WITH    data AS
(
SELECT  1000 AS number
UNION ALL
SELECT  1001
UNION ALL
SELECT  1002
UNION ALL
SELECT  1003
UNION ALL
SELECT  1005
UNION ALL
SELECT  1006
UNION ALL
SELECT  1008
),
rows AS
(
SELECT  q2.number AS nnumber, q.number AS number
FROM    (
SELECT  number
FROM    data di
WHERE   NOT EXISTS
(
SELECT  NULL
FROM    data dn
WHERE   dn.number = di.number - 1
)
) q
OUTER APPLY
(
SELECT  TOP 1 number
FROM    data dp
WHERE   dp.number < q.number
ORDER BY
dp.number DESC
) q2
UNION ALL
SELECT  TOP 1 number, NULL
FROM    data
ORDER BY
number DESC
),
rns AS
(
SELECT  *, ROW_NUMBER() OVER (ORDER BY nnumber) AS rn
FROM    rows
)
SELECT  re.number, rb.nnumber
FROM    rns re
JOIN    rns rb
ON      rb.rn = re.rn + 1
``````