SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 please help me to get
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

born2acheive
Yak Posting Veteran

65 Posts

Posted - 08/17/2006 :  02:22:53  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 08/17/2006 :  03:46:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This is one way to go, with the help of this function http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685.
-- 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 - 08/17/2006 :  04:48:21  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 08/17/2006 :  04:54:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 08/17/2006 04:55:52
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 08/17/2006 :  04:57:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 08/17/2006 :  23:16:31  Show Profile  Reply with Quote
hey peso very thanks ,wonderful and very helpful code u given, i got answer,thanks a lottttttttttttttttttttttttttttttt
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000