Function to return broken Integer range for unique

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Function to return broken Integer range for unique

Author

Topic

tpa32
Starting Member

3 Posts

Posted - 2010-10-05 : 16:46:57

Hi All,
I need to write a function which will return me a range of integers assigned to some unique ID.
my sample data is as follow.

TableID UniqueID No
93986 10777 283
93987 10777 284
93988 10777 285
93989 10777 286
93990 10777 287
93991 10777 288
93992 10777 289
93993 10777 290
93994 10777 291
93995 10777 292
93996 10777 1353
93997 10777 1354
93998 10777 1355
93999 10777 1356
94000 10777 1357
94001 10777 1358
94002 10777 1359
94003 10777 1360
94004 10777 1361
94005 10777 1362

I would expect the return result for 10777 uniqueID would be
283-292,1353-1362
if data is as follows:-

TableID UniqueID No
93986 10777 283
93988 10777 285
93989 10777 286
93990 10777 287
93991 10777 288
93992 10777 289
93993 10777 290
93994 10777 291
93995 10777 292
93996 10777 1353
93997 10777 1354
93999 10777 1356
94000 10777 1357
94001 10777 1358
94002 10777 1359
94003 10777 1360
94004 10777 1361
94005 10777 1362

then result would be like

283,285-292,1353-1354,1356-1362
and if data is as follow
93996 10777 1353
93997 10777 1354
93999 10777 1356
94000 10777 1357
94001 10777 1358
94002 10777 1359
94003 10777 1360
94004 10777 1361
94005 10777 1362

then range would be something like
1353-1362

Thanks

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 02:15:51

[code]
declare @mntbl as table(id int)
insert into @mntbl
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 8 union all
select 9 union all
select 11

select convert(varchar(20),min(id)) + '-' + convert(varchar(20),max(id))range from
(
select *,id-row_number()over(order by id)rid from @mntbl
)t group by rid
[/code]

PBUH

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-10-06 : 02:36:10

enhance this to get yr results

declare @Maintable as table
(
TableID int,
uniqueID int,
number int
)

insert into @Maintable

select 93986, 10777 ,283
union select 93987, 10777 ,284
union select 93988, 10777 ,285
union select 93989, 10777 ,286
union select 93990, 10777 ,287
union select 93991, 10777 ,288
union select 93992, 10777 ,289 union select
93993, 10777 ,290 union select
93994 ,10777 ,291 union select
93995, 10777 ,292 union select
93996 ,10777 ,1353 union select
93997 ,10777 ,1354 union select
93998 ,10777 ,1355 union select
93999 ,10777 ,1356 union select
94000 ,10777 ,1357 union select
94001 ,10777 ,1358 union select
94002 ,10777 ,1359 union select
94003 ,10777 ,1360 union select
94004 ,10777 ,1361 union select
94005 ,10777, 1362
;with cte as
(
select *,ROW_NUMBER() over (order by number asc) localindex from @Maintable
)
,cte2 as
(
select number,number - isnull((select number from cte s where s.localindex=b.localindex-1),0) diff,localindex
from cte b)
,cte3 as
(
select * from cte2 a
where diff <> 1 or isnull((select DIFF from cte2 b where b.localindex = a.localindex+1),0) <>1
)

select * from cte3 order by localindex asc

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 02:54:37

Kunal thx for the sample data


select convert(varchar(20),min(number)) + '-' + convert(varchar(20),max(number))range from
(
			select *,number-row_number()over(order by number)rid from @Maintable 
			
)t group by rid

o/p


range
-----------------------------------------
283-292
1353-1362

PBUH

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-10-06 : 03:16:09

gd one

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 03:29:19

quote:
Originally posted by kunal.mehta

gd one

Thanks

PBUH

tpa32
Starting Member

3 Posts

Posted - 2010-10-06 : 10:37:13

Thanks you all for your reply.
I rather generated a string in one column from multiple rows.
and I will handle the range in my c# code.
I used this code to get me a string.

Select Main.SubjectID,
Left(Main.Students,Len(Main.Students)-1) As "Students"
From(Select distinct ST2.SubjectID,
(Select ST1.StudentName + ',' AS [text()]
From dbo.Students ST1
Where ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
For XML PATH ('')) [Students]
From dbo.Students ST2) [Main]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources