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.
Author |
Topic |
born2acheive
Yak Posting Veteran
65 Posts |
Posted - 2006-08-17 : 02:22:53
|
i have datas like thisid from to1 500 7001 600 9001 650 9501 1000 1100i need ans like this: id from to 1 500 950 1 1000 1100becos 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 datadeclare @test table ([id] int, [from] int, [to] int)insert @testselect 2, 500, 700 union allselect 1, 500, 700 union allselect 1, 600, 900 union allselect 1, 650, 950 union allselect 1, 1000, 1100-- do the workdeclare @min int, @max intselect @min = min([from]), @max = max([to])from @testdeclare @stage table ([id] int, number int)insert @stage ( [id], number )select t.[id], f.numberfrom f_table_number_range(@min, @max) finner join @test t on f.number between t.[from] and t.[to]order by t.[id], f.numberdeclare @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 + 1from @stage s0left join @stage s1 on s1.[id] = s0.[id] and s1.number = s0.number + 1left join @stage s2 on s2.[id] = s0.[id] and s2.number = s0.number - 1where s1.[id] is null or s2.[id] is nullorder by s0.[id], s0.number-- show the outputselect max([id]) [id], max([from]) [from], max([to]) [to]from @resultgroup by row / 2order by [id], [from], [to] Peter LarssonHelsingborg, Sweden |
|
|
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 20Invalid object name 'f_table_number_range'. actually my table name is testplease 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 |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 integersin the range of@START_NUMBER through @END_NUMBER, inclusive.The maximum number of rows that this function can returnis 16777216.*/returns table asreturn(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 endfrom ( 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 ) bwhere 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 = 1order by 1)GOGRANT SELECT ON [dbo].[F_TABLE_NUMBER_RANGE] TO [public]GO Peter LarssonHelsingborg, Sweden |
|
|
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 |
|
|
|
|
|
|
|