Want to execute very strange query

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 2005 Forums

Transact-SQL (2005)

Want to execute very strange query

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2007-08-14 : 00:07:32

Hi freinds!!

I want to execute very strange query can you help me out in that!

see my two input for the SP is
@Start int
@End int
@Interval int

input case I
@Start = 78 @End = 80 @Interval = 1000 metre

my out from this is...1st row is header of table and remaining rows are records that I want......output table:

Point Type------Point Desc----Point Length in meters
Point Start------78------------1000
Point Stone--------79------------1000
Point End--------80------------0

input case II
@Start = 78 @End = 80 @Interval = 200 metre

output table:

Point Type------Point Desc------Point Length in meters
Point Start-----78--------------200
Point Stone-----79/2------------200
Point Stone-----79/4------------200
Point Stone-----79/6------------200
Point Stone-----79/8------------200
Point Stone-----79--------------200
Point Stone-----80/2------------200
Point Stone-----80/4------------200
Point Stone-----80/6------------200
Point Stone-----80/8------------200
Point End-------80--------------0

table structure is
Point Type varchar(100)
Point Desc varchar(100)
Point Length in meters varchar(100)

T.I.A

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-08-14 : 02:41:02

try this..
select * from tablename where substring([point desc],1,case charindex('/',[point desc]) when 0 then len([point desc]) else charindex('/',[point desc]) -1 end ) between '78' and '80' and ([point length] = 200 or [point length]= 0) order by [point length] desc

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2007-08-14 : 05:26:36

I dont have table i have to get out put as shown by passing these inputs

T.I.A

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-08-14 : 06:39:18

Hi,
In this case you have to calculate the no of steps required to reach from start to end
@stepsrequired = (@end - @start) * 1000/ @interval
and then you have to loop thru the steps required

declare @start int, @end int, @interval int
set @Start = 78
set @End = 80
set @Interval = 200
declare @steps int
set @steps = @start *1000
declare @desc varchar(20)
declare @i int
set @i = 1
while @i <= ((@end - @start)*1000) /@interval
begin
if @i = 1
set @desc = 'Point Start'
else if @i < ((@end - @start)*1000) /@interval
set @desc = 'Point Stone'
else
set @desc = 'Point end'
select @desc, @steps + @interval , @interval
set @steps = @steps + @interval
set @i = @i+1
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources