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
 General SQL Server Forums
 New to SQL Server Programming
 please help me to get

Author  Topic 

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-08-17 : 02:22:53
i have datas like this

id from to
1 500 700
1 600 900
1 650 950
1 1000 1100

i need ans like this:
id from to
1 500 950
1 1000 1100

becos 600 lies between 500 to 700 and 650 lies between to 600 to 900 but 1000,1100 in not lising so i neet output like that,please give me query to do that pleaseeeeeeeeeee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 03:46:25
This is one way to go, with the help of this function [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685[/url].
-- prepare test data
declare @test table ([id] int, [from] int, [to] int)

insert @test
select 2, 500, 700 union all
select 1, 500, 700 union all
select 1, 600, 900 union all
select 1, 650, 950 union all
select 1, 1000, 1100

-- do the work
declare @min int,
@max int

select @min = min([from]),
@max = max([to])
from @test

declare @stage table ([id] int, number int)

insert @stage
(
[id],
number
)
select t.[id],
f.number
from f_table_number_range(@min, @max) f
inner join @test t on f.number between t.[from] and t.[to]
order by t.[id],
f.number

declare @result table ([id] int, [from] int, [to] int, row int identity(0, 1))

insert @result
(
[id],
[from],
[to]
)
select s0.[id],
s1.number - 1,
s2.number + 1
from @stage s0
left join @stage s1 on s1.[id] = s0.[id] and s1.number = s0.number + 1
left join @stage s2 on s2.[id] = s0.[id] and s2.number = s0.number - 1
where s1.[id] is null
or s2.[id] is null
order by s0.[id],
s0.number

-- show the output
select max([id]) [id],
max([from]) [from],
max([to]) [to]
from @result
group by row / 2
order by [id],
[from],
[to]


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-08-17 : 04:48:21
hi peso,
i run ur query,it shows error like this:

(5 row(s) affected)

Server: Msg 208, Level 16, State 3, Line 20
Invalid object name 'f_table_number_range'.
actually my table name is test
please tell me what is f_table_number_range in ur query,i just changed my table name as f_table_number_range then also the same error comming,if i use test also the same error i am getting,please tell me how to solve this error.pleaseeeee
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 04:54:18
You have to click on the provided link and copy the code for the function and run the code at your server first to "install" the F_TABLE_NUMBER_RANGE to you SQL Server.

Then run my code, after changing my tablenames and columnnames to the proper names in your environment.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 04:57:09
If you can't figure out where the function is, here is the code. Copy and paste to your server and run this FIRST!
create function dbo.F_TABLE_NUMBER_RANGE
(
@START_NUMBER int,
@END_NUMBER int
)
/*
This function returns an integer table containing all integers
in the range of@START_NUMBER through @END_NUMBER, inclusive.
The maximum number of rows that this function can return
is 16777216.
*/

returns table
as

return
(
select top 100 percent
NUMBER = (a.NUMBER+b.NUMBER)+
-- Add the starting number for the final result set
-- The case is needed, because the start and end
-- numbers can be passed in any order
case
when @START_NUMBER <= @END_NUMBER
then @START_NUMBER
else @END_NUMBER
end
from
(
Select top 100 percent
NUMBER = convert(int,N01+N02+N03)
From
-- Cross rows from 3 tables based on powers of 16
-- Maximum number of rows from cross join is 4096, 0 to 4095
( select N01 = 0 union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all
select 15 ) n01
cross join
( select N02 = 0 union all select 16 union all select 32 union all
select 48 union all select 64 union all select 80 union all
select 96 union all select 112 union all select 128 union all
select 144 union all select 160 union all select 176 union all
select 192 union all select 208 union all select 224 union all
select 240 ) n02
cross join
( select N03 = 0 union all select 256 union all select 512 union all
select 768 union all select 1024 union all select 1280 union all
select 1536 union all select 1792 union all select 2048 union all
select 2304 union all select 2560 union all select 2816 union all
select 3072 union all select 3328 union all select 3584 union all
select 3840 ) n03
where
-- Minimize the number of rows crossed by selecting only rows
-- with a value less the the square root of rows needed.
N01+N02+N03 <
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
order by
1
) a
cross join
(
Select top 100 percent
NUMBER =
convert(int,
(N01+N02+N03) *
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
)
From
-- Cross rows from 3 tables based on powers of 16
-- Maximum number of rows from cross join is 4096, 0 to 4095
( select N01 = 0 union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all
select 15 ) n01
cross join
( select N02 = 0 union all select 16 union all select 32 union all
select 48 union all select 64 union all select 80 union all
select 96 union all select 112 union all select 128 union all
select 144 union all select 160 union all select 176 union all
select 192 union all select 208 union all select 224 union all
select 240 ) n02
cross join
( select N03 = 0 union all select 256 union all select 512 union all
select 768 union all select 1024 union all select 1280 union all
select 1536 union all select 1792 union all select 2048 union all
select 2304 union all select 2560 union all select 2816 union all
select 3072 union all select 3328 union all select 3584 union all
select 3840 ) n03
where
-- Minimize the number of rows crossed by selecting only rows
-- with a value less the the square root of rows needed.
N01+N02+N03 <
-- Square root of total rows rounded up to next whole number
convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1)))
order by
1
) b
where
a.NUMBER+b.NUMBER <
-- Total number of rows
abs(@START_NUMBER-@END_NUMBER)+1 and
-- Check that the number of rows to be returned
-- is less than or equal to the maximum of 16777216
case
when abs(@START_NUMBER-@END_NUMBER)+1 <= 16777216
then 1
else 0
end = 1
order by
1
)

GO
GRANT SELECT ON [dbo].[F_TABLE_NUMBER_RANGE] TO [public]
GO


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-08-17 : 23:16:31
hey peso very thanks ,wonderful and very helpful code u given, i got answer,thanks a lottttttttttttttttttttttttttttttt
Go to Top of Page
   

- Advertisement -