running manipulation of columns in sql query

Question

Post reply

running manipulation of columns in sql query

Sash Mav

SSC Enthusiast

Points: 131
More actions
May 8, 2010 at 9:55 am

#234824

Hi guys,
I have a table that consist of a 3 columns , id, strike, customstrike. The requirement is that if the difference between the values of 2 consecutive strike column is less than 30 then do a on the fly computation and so that the strike column value differ by 30.
Here is the definition of table.
if object_id('tempdb..#tmp') is not null
drop table #tmp
create table #tmp
(
num int,
strike int,
customstrike int
)
go
insert into #tmp
values (1, 1075,null)
insert into #tmp
values (2, 1085,null)
insert into #tmp
values (3, 1255,null)
insert into #tmp
values (4, 1285,null)
insert into #tmp
values (5, 1355,null)
insert into #tmp
values (6, 1365,null)
select * from #tmp order by strike
now the output i want is
num strike customstrike
1 1075 1075
2 1085 1105
3 1255 1255
4 1285 1285
5 1355 1355
6 1365 1385
Basically at any point of time, the difference b/w values of customstrike column should not be less than 30. Any help would be greatly appreciated

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sash Mav SSC Enthusiast Points: 131 More actions · Answer 1

never mind..fellas

here u go

select

t.num ,

t.strike ,

rt.col2

from #tmp t

cross apply (select

case when (t.strike - strike) < 10 then (t.strike + 30 - (t.strike - strike))

end as col2

from #tmp

where strike <= t.strike

) as rt

where col2 is not null

order by t.num

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

Sash Mav (5/8/2010)
never mind..fellas
here u go
select
t.num ,
t.strike ,
rt.col2
from #tmp t
cross apply (select
case when (t.strike - strike) < 10 then (t.strike + 30 - (t.strike - strike))
end as col2
from #tmp
where strike <= t.strike
) as rt
where col2 is not null
order by t.num

Cool... thanks for posting your answer. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)