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
