Make Sequential numbers into ranges

CREATE TABLE #Temp
(TNID INT, TN VARCHAR(20))

INSERT INTO #Temp
SELECT 3011170, '4402787100'
UNION ALL SELECT 3011170, '4402787101'
UNION ALL SELECT 3011170, '4402787102'
UNION ALL SELECT 3011170, '4402787103'
UNION ALL SELECT 3011171, '4402787104'
UNION ALL SELECT 3011171, '4402787105'
UNION ALL SELECT 3011175, '4402787118'
UNION ALL SELECT 3011175, '4402787119'
UNION ALL SELECT 3011175, '4405165555'
UNION ALL SELECT 3011175, '4405165556'
UNION ALL SELECT 3011175, '4405165557'
UNION ALL SELECT 3011175, '4405165558'
UNION ALL SELECT 3011175, '4409447976'

SELECT TNID, TNMIN=MIN(TN), TNMAX=MAX(TN)
FROM #Temp
GROUP BY TNID

DROP TABLE #Temp

CREATE TABLE #Temp
(TNID INT, TN BIGINT);

INSERT INTO #Temp
SELECT 3011170, 4402787100
UNION ALL SELECT 3011170, 4402787101
UNION ALL SELECT 3011170, 4402787102
UNION ALL SELECT 3011170, 4402787103
UNION ALL SELECT 3011171, 4402787104
UNION ALL SELECT 3011171, 4402787105
UNION ALL SELECT 3011175, 4402787118
UNION ALL SELECT 3011175, 4402787119
UNION ALL SELECT 3011175, 4405165555
UNION ALL SELECT 3011175, 4405165556
UNION ALL SELECT 3011175, 4405165557
UNION ALL SELECT 3011175, 4405165558
UNION ALL SELECT 3011175, 4409447976;

with rCTE as (
select
row_number() over (order by (select null)) rn,
t1.TNID,
t1.TN
from
#Temp t1
left outer join #Temp t2
on t1.TNID = t2.TNID and t1.TN = t2.TN + 1
where
t2.TNID is null
union all
select
rn,
t1.TNID,
t1.TN
from
#Temp t1
inner join rCTE t2
on t1.TNID = t2.TNID and t1.TN = t2.TN + 1
)
select TNID, min(TN) TN1, max(TN) TN2 from rCTE group by rn, TNID order by rn,TNID;

go

drop table #Temp;
go

GSquared (8/28/2012)

Lynn Pettis (8/28/2012)

Here is my shot at the solution.


CREATE TABLE #Temp
    (TNID INT, TN BIGINT);

INSERT INTO #Temp
SELECT 3011170, 4402787100
UNION ALL SELECT 3011170, 4402787101
UNION ALL SELECT 3011170, 4402787102
UNION ALL SELECT 3011170, 4402787103
UNION ALL SELECT 3011171, 4402787104
UNION ALL SELECT 3011171, 4402787105
UNION ALL SELECT 3011175, 4402787118
UNION ALL SELECT 3011175, 4402787119
UNION ALL SELECT 3011175, 4405165555
UNION ALL SELECT 3011175, 4405165556
UNION ALL SELECT 3011175, 4405165557
UNION ALL SELECT 3011175, 4405165558
UNION ALL SELECT 3011175, 4409447976;

with rCTE as (
select
    row_number() over (order by (select null)) rn,
    t1.TNID,
    t1.TN
from
    #Temp t1
    left outer join #Temp t2
        on t1.TNID = t2.TNID and t1.TN = t2.TN + 1
where
    t2.TNID is null
union all
select
    rn,
    t1.TNID,
    t1.TN
from
    #Temp t1
    inner join rCTE t2
        on t1.TNID = t2.TNID and t1.TN = t2.TN + 1
)
select TNID, min(TN) TN1, max(TN) TN2 from rCTE group by rn, TNID order by rn,TNID;

go

drop table #Temp;
go

You'll run into maxrecursion issues if the actual data has any significant size to it at all, with this solution.

CREATE TABLE #Temp
(TNID INT, TN BIGINT);

INSERT INTO #Temp
SELECT 3011170, 4402787100
UNION ALL SELECT 3011170, 4402787101
UNION ALL SELECT 3011170, 4402787102
UNION ALL SELECT 3011170, 4402787103
UNION ALL SELECT 3011171, 4402787104
UNION ALL SELECT 3011171, 4402787105
UNION ALL SELECT 3011175, 4402787118
UNION ALL SELECT 3011175, 4402787119
UNION ALL SELECT 3011175, 4405165555
UNION ALL SELECT 3011175, 4405165556
UNION ALL SELECT 3011175, 4405165557
UNION ALL SELECT 3011175, 4405165558
UNION ALL SELECT 3011175, 4409447976;

with basedata as (
select
TNID,
TN,
TN - row_number() over (order by TNID, TN) as grp
from
#Temp
)
select
TNID,
min(TN) as TN1,
max(TN) as TN2
from
basedata
group by
grp, TNID
;

go

drop table #Temp;
go