May 13, 2009 at 11:27 am
Please help!
Here is a table with simple values
create table #LAYOUT
(
id int identity primary key,
start int,
[length] int
)
insert into #LAYOUT (start, [length]) values(1, 10)
insert into #LAYOUT ([length]) values(1 )
insert into #LAYOUT ([length]) values(9)
insert into #LAYOUT ([length]) values(12)
insert into #LAYOUT ([length]) values(3)
insert into #LAYOUT ([length]) values(4)
select * from #layout
drop table #LAYOUT
I need to calculate values of a start column meaning that start equals to sum of the values of the start and the length columns of the previous record
In this case the result set should look like this
start length
----- ------
1 10
11 1
12 9
21 12
33 3
36 4
This needs to be done by one query.
Thanks
May 13, 2009 at 2:47 pm
create table #LAYOUT
(
id int identity CONSTRAINT PK_layout primary key clustered,
start int,
[length] int
)
insert into #LAYOUT (start, [length]) values(1, 10)
insert into #LAYOUT ([length]) values(1 )
insert into #LAYOUT ([length]) values(9)
insert into #LAYOUT ([length]) values(12)
insert into #LAYOUT ([length]) values(3)
insert into #LAYOUT ([length]) values(4)
select * from #layout
declare @l int
select @l = 0
update #layout SET @l = @l + [length],start = COALESCE(start,@l)
select * from #layout
drop table #LAYOUT
* Noel
May 13, 2009 at 2:55 pm
Thanks, Noel.
Yep, this is one of the options.
Here is the solution I was looking for:
create table #LAYOUT
(
id int identity primary key,
start int,
[length] int
)
insert into #LAYOUT (start, [length]) values(1, 10)
insert into #LAYOUT ([length]) values(1 )
insert into #LAYOUT ([length]) values(9)
insert into #LAYOUT ([length]) values(12)
insert into #LAYOUT ([length]) values(3)
insert into #LAYOUT ([length]) values(4)
select
(select start from #layout where id = 1) +
(select isnull(sum(length),0) from #layout L2 where L2.id < L1.id)
as start, [length]
from #layout L1
drop table #LAYOUT
May 13, 2009 at 3:02 pm
us_dba (5/13/2009)
Thanks, Noel.Yep, this is one of the options.
Here is the solution I was looking for:
Why were you asking if you already knew what you were looking for?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 4:29 pm
I'm with Barry. Why waste people's time?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 13, 2009 at 11:43 pm
I am sorry guys, but don’t get me wrong. I have found the answer after I had posted the question. I guess it still might be helpful for somebody. Thanks for your effort!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply