Make ranges with consecutive ports # and system#

Transact-SQL
1

Hi ,

I need to build range of consecutive ports numbers along with its system#
my table could be like the following

Port# System#
1 03T1
2 03T1
3 03T1
4 03T1
5 03T1
6 03T1
7 03T1
8 03T1
9 03T1
10 03T1
11 03T1
12 03T1
169 03T1
170 03T1
171 03T1
172 03T1
173 03T1
174 03T1
175 03T1
176 03T1
177 03T1
178 03T1
179 03T1
180 03T1
133 04T1
134 04T1
135 04T1
136 04T1
137 04T1
138 04T1
139 04T1
140 04T1
141 04T1
142 04T1
143 04T1
144 04T1
181 04T1
182 04T1
183 04T1
184 04T1
185 04T1
186 04T1
187 04T1
188 04T1
189 04T1
190 04T1
191 04T1
192 04T1

The results I want to get would be like this format.

Port# System#
0001-0012 03T1
0169-0180 03T1
0133-0192 04T1

Thanks for any help in advance!

2

What is the max port length?

6

hi

i tried to do this ...

earlier i gave a solution
... using recursive cte
but recursive cte's are very bad for performance

so i came up with a very easy solution

please click arrow to the left for drop create data ... 
drop table data 
go 

create table data 
(
row_num int identity(1,1) not null,
PortNumber int, 
SystemNumber varchar(100) 
)
go 

insert into data select 1,'03T1'
insert into data select 2,'03T1'
insert into data select 3,'03T1'
insert into data select 4,'03T1'
insert into data select 5,'03T1'
insert into data select 6,'03T1'
insert into data select 7,'03T1'
insert into data select 8,'03T1'
insert into data select 9,'03T1'
insert into data select 10,'03T1'
insert into data select 11,'03T1'
insert into data select 12,'03T1'
insert into data select 169,'03T1'
insert into data select 170,'03T1'
insert into data select 171,'03T1'
insert into data select 172,'03T1'
insert into data select 173,'03T1'
insert into data select 174,'03T1'
insert into data select 175,'03T1'
insert into data select 176,'03T1'
insert into data select 177,'03T1'
insert into data select 178,'03T1'
insert into data select 179,'03T1'
insert into data select 180,'03T1'
insert into data select 133,'04T1'
insert into data select 134,'04T1'
insert into data select 135,'04T1'
insert into data select 136,'04T1'
insert into data select 137,'04T1'
insert into data select 138,'04T1'
insert into data select 139,'04T1'
insert into data select 140,'04T1'
insert into data select 141,'04T1'
insert into data select 142,'04T1'
insert into data select 143,'04T1'
insert into data select 144,'04T1'
insert into data select 181,'04T1'
insert into data select 182,'04T1'
insert into data select 183,'04T1'
insert into data select 184,'04T1'
insert into data select 185,'04T1'
insert into data select 186,'04T1'
insert into data select 187,'04T1'
insert into data select 188,'04T1'
insert into data select 189,'04T1'
insert into data select 190,'04T1'
insert into data select 191,'04T1'
insert into data select 192,'04T1'
go
please click arrow to the left for EASY way SQL 
;WITH cte_grp 
     AS (SELECT portnumber - row_num AS grp, 
                * 
         FROM   data) 
SELECT 'SQL Output', 
       Min(RIGHT(Concat('0000', portnumber), 4)) 
       + '-' 
       + Max(RIGHT(Concat('0000', portnumber), 4)) AS PortNumber, 
       systemnumber, 
       grp 
FROM   cte_grp 
GROUP  BY grp, 
          systemnumber

image
image920×285 5.78 KB

2 Likes
7

Hi harish reddy,

Your solution works like a charm. Thank you so much for your help!

8

Nice!