T-SQL Collapsing numbers that are in continous order

I have the following numbers:
ID      BeginNumber      EndNumber
1      16                              29
2      29                             40
3      5514                     5549
4      7586                     7631
6      14741                    14879
7      14879                    14978
8      15135                     15137
9      15137                     15154
10      15154                    15156
11      15156                   15157
12      18937                   18938
13      18938                   18939
14      18939                  19005

If the numbers are continuing from the previous record, I would like to collapse the record into one, otherwise keep the current record.  For the example above the result set would be

Result      
BeginNumber      EndNumber
16                             40
5514                     5549
7586                    7631
14741                    14978
15135                     15157
18937                    19005

In the result, the first record is collapsed into one since it is continuous in the first result set (16 to 29 and 29 to 40).
5514 and 5549 is kept as is because it is not continuous from the previous record.

I appreciate the help!
yanci1179Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
What version of SQL are you running?
1
Brian CroweDatabase AdministratorCommented:
Assuming SQL 2012 or later:

DECLARE @Test TABLE
(
	ID			INT,
	BeginNumber	INT,
	EndNumber	INT
);

INSERT INTO @Test (ID, BeginNumber, EndNumber)
VALUES
	(1 ,     16        ,                      29 ),
	(2 ,     29        ,                     40	 ),
	(3 ,     5514      ,               5549		 ),
	(4 ,     7586      ,               7631		 ),
	(6 ,     14741     ,               14879	 ),
	(7 ,     14879     ,               14978	 ),
	(8 ,     15135     ,                15137	 ),
	(9 ,     15137     ,                15154	 ),
	(10,      15154    ,                15156	 ),
	(11,      15156    ,               15157	 ),
	(12,      18937    ,               18938	 ),
	(13,      18938    ,               18939	 ),
	(14,      18939    ,              19005		 );

WITH cteTest AS
(
	SELECT ID, BeginNumber, EndNumber,
		LAG(EndNumber, 1, NULL) OVER(ORDER BY BeginNumber) AS PrevEndNumber,
		CASE WHEN BeginNumber - LAG(EndNumber) OVER(ORDER BY BeginNumber) = 0 THEN NULL ELSE ROW_NUMBER() OVER(ORDER BY BeginNumber) END AS Grp
	FROM @Test
),
cteGrp AS
(
	SELECT ID, BeginNumber, EndNumber,
		MAX(Grp) OVER(ORDER BY BeginNumber) AS Grp
	FROM cteTest
)
SELECT MIN(BeginNumber) AS BeginNumber, MAX(EndNumber) AS EndNumber
FROM cteGrp
GROUP BY Grp

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kyle AbrahamsSenior .Net DeveloperCommented:
-- TABLE GENERATION
select 1 a, 16 b, 29 e
into #temp

update #work set b = 16 where a = 1

insert into #temp
select 2,      29        ,                     40
union select 3,      5514,                     5549
union select 4 ,     7586 ,                    7631
union select 6,      14741,                    14879
union select 7,    14879   ,                 14978
union select 8,      15135  ,                   15137
union select 9,      15137   ,                  15154
union select 10,      15154   ,                 15156
union select 11,      15156    ,               15157
union select 12,      18937     ,              18938
union select 13,      18938      ,             18939
union select 14,      18939       ,           19005

-- END TABLE GENERATION

-- BEGIN SOLUTION
;with cte as(
select *, ROW_NUMBER() over (order by a) row, 0 as ContinuesNextRow, 0 MaxEndSeries 

from #temp
)



select * into #work from cte

update w
set ContinuesNextRow = 1
from
#work w
join #work w2 on w.row + 1 = w2.row and w.e = w2.b

update w set 
MaxEndSeries = isnull((
select MAX(e) from #work 
where ContinuesNextRow = 0 
and row >= w.row and row <= isnull((select min(row) from #work where row >= w.Row and ContinuesNextRow = 0),row)
),e)
from #work w

select * from #work
select MIN(b), MaxEndSeries 
from #work
group by MaxEndSeries

-- END SOLUTION

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.