yanci1179
asked on
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!
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!
Brian Crowe
What version of SQL are you running?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
-- 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