Generate all the continous intervals from a series

Question

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

Answer 1

The easy way out is to have a table with the TaxLabelNumbers so that you can do an outer join.

It's also possible to create that kind of a table in CTE but it's not very efficient.

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

CTE wisely defaults to 100 recursions so you need to crank that up if you need more than 100 numbers:

select * from TaxLabelSeq option (MaxRecursion 4711)

Answer 2

Try this

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
      /* now join each START with every END above it */
      SEND.num >= SSTART.num
      /* we group over each START, so we can get the corresponding END with MIN */
GROUP BY SSTART.num

Answer 3

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