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 . 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