Alex Rivera | Logout

Group close numbers

Asked 2011-08-31T14:27:08.127
11

I have a table with 2 columns of integers. The first column represents start index and the second column represents end index.

START END
1     8
9     13
14    20
20    25
30    42
42    49
60    67

Simple So far. What I would like to do is group all the records that follow together:

START END
1     25
30    49
60    67

A record can follow by Starting on the same index as the previous end index or by a margin of 1:

START END
1     10
10    20

And

START END
1     10
11    20

will both result in 

START END
1     20

I'm using SQL Server 2008 R2.

Any help would be Great

sql sql-server sql-server-2008 gaps-and-islands
Edit
Report

1 Answer

4

This works for your example, let me know if it doesn't work for other data

create table #Range 
(
  [Start] INT,
  [End] INT
)

insert into #Range ([Start], [End]) Values (1, 8)
insert into #Range ([Start], [End]) Values (9, 13)
insert into #Range ([Start], [End]) Values (14, 20)
insert into #Range ([Start], [End]) Values (20, 25)
insert into #Range ([Start], [End]) Values (30, 42)
insert into #Range ([Start], [End]) Values (42, 49)
insert into #Range ([Start], [End]) Values (60, 67)



;with RangeTable as
(select
    t1.[Start],
    t1.[End],
    row_number() over (order by t1.[Start]) as [Index]
from
    #Range t1
where t1.Start not in (select 
                      [End] 
               from
                  #Range
                  Union
               select 
                  [End] + 1
               from
                  #Range
               )
)
select 
    t1.[Start],
    case 
   when t2.[Start] is null then
        (select max([End])
                     from #Range)
       else
        (select max([End])
                     from #Range
                     where t2.[Start] > [End])
end as [End]    
from 
    RangeTable t1
left join 
    RangeTable t2
on
    t1.[Index] = t2.[Index]-1 

drop table #Range;
answered 2011-08-31T15:45:10.103

Your Answer