How to subtract previous value of one column from current value of another column

I have a table like this:

BottomTop

02

46.5

914

1517

In Transact SQL I need to calculate the difference between the current bottom and the previous top. If there is no previous top, then the result would be the current bottom.

So, the result would be 0, 2, 2.5, 1

I must return the value in a table. Thank you all for your help!

Karen

If you have a column to sequence by then you can try something similar to this:

create table test(seq int identity, bottom float, [top] float)

insert into test values(0,2)

insert into test values(4,6.5)

insert into test values(9,14)

insert into test values(15,17)

select CASE WHEN (SELECT B.[top] FROM test B WHERE B.seq = (SELECT MAX(seq) FROM test WHERE seq < A.seq)) IS NULL

THEN

A.bottom

ELSE

A.bottom - (SELECT B.[top] FROM test B WHERE B.seq = (SELECT MAX(seq) FROM test WHERE seq < A.seq))

END

from test A

order by A.seq

|||

Karen,

If you are using SQL Server 2005, you can take advantage of the

new analytic functions more or less like this:

create table T(

primary_key int identity(1,1) primary key,

bottom decimal(10,2),

[top] decimal(10,2)

)

create index T_bottom on T(bottom,primary_key) include ([top])

insert into T values (0,2)

insert into T values (4,6.5)

insert into T values (9,14)

insert into T values (15,17)

go

with Tnumbered as (

select

bottom,

[top],

row_number() over (order by bottom, primary_key) as rownum

from T

)

select

this.bottom,

this.[top],

this.bottom - coalesce(prev.[top],0) as bottom_minus_prev_top

from Tnumbered as this

left outer join Tnumbered as prev

on this.rownum = prev.rownum + 1

go

drop table T

-- Steve Kass

-- Drew University

-- http://www.stevekass.com

mz1derful@.discussions.microsoft.com wrote:

> I have a table like this:

>

> Bottom Top

>

> 0 2

>

> 4 6.5

>

> 9 14

>

> 15 17

>

> In Transact SQL I need to calculate the difference between the current

> bottom and the previous top. If there is no previous top, then the

> result would be the current bottom.

>

> So, the result would be 0, 2, 2.5, 1

>

> I must return the value in a table. Thank you all for your help!

>

> Karen

>

>

|||

SELECT MyTop, MyBottom,
CASE WHEN MyTop = 0 OR MyBottom = 0 THEN 0 ELSE (MyBottom/MyTop) END AS [Difference]
FROM MyTestTable

Adamus

|||

NNTP User wrote:

Karen,

If you are using SQL Server 2005, you can take advantage of the

new analytic functions more or less like this:

create table T(

primary_key int identity(1,1) primary key,

bottom decimal(10,2),

[top] decimal(10,2)

)

create index T_bottom on T(bottom,primary_key) include ([top])

insert into T values (0,2)

insert into T values (4,6.5)

insert into T values (9,14)

insert into T values (15,17)

go

with Tnumbered as (

select

bottom,

[top],

row_number() over (order by bottom, primary_key) as rownum

from T

)

select

this.bottom,

this.[top],

this.bottom - coalesce(prev.[top],0) as bottom_minus_prev_top

from Tnumbered as this

left outer join Tnumbered as prev

on this.rownum = prev.rownum + 1

go

drop table T

-- Steve Kass

-- Drew University

-- http://www.stevekass.com

mz1derful@.discussions.microsoft.com wrote:

> I have a table like this:

>

> Bottom Top

>

> 0 2

>

> 4 6.5

>

> 9 14

>

> 15 17

>

> In Transact SQL I need to calculate the difference between the current

> bottom and the previous top. If there is no previous top, then the

> result would be the current bottom.

>

> So, the result would be 0, 2, 2.5, 1

>

> I must return the value in a table. Thank you all for your help!

>

> Karen

>

>

This code works great if it's static and you already have the answer.

Other than that...Programming 101

Adamus

|||

What do you mean by this? Does the code not work? Why wouldn't the code be static? No problem saying something is wrong but please substantiate, otherwise you are just being negative.

|||

The recordset will ALWAYS be dynamic.

You have no idea what the values will be.

Adamus

|||So are you saying that Steve's code only works for those particular values?|||

Louis Davidson wrote:

So are you saying that Steve's code only works for those particular values?

That is correct. Hence, the hard coding.

You could populate the table dynamically but what would be the point of going to that extreme when a simple solution exists?

...and please don't preach performance on such a petty problem.

Adamus

|||

Louis Davidson wrote:

What do you mean by this? Does the code not work? Why wouldn't the code be static? No problem saying something is wrong but please substantiate, otherwise you are just being negative.

Just because the code works and/or might be clever doesn't mean it's the best solution.

Adamus