Calculation between rows in one field

Hi,
I need to do a calculations on an amount field between rows in one field in
SQL Server 2000, i.e., (current_amount - previous_amount/previous_amount).
Let's say we have the following:
Quarter Amount
Q1 200000
Q2 150000
Q3 125000
Q4 75000
I really appreciate your help,
SKuntested.. but it should be in these lines. Hope this helps :)
select a. quarter, a.amount, (a.amount-b.amount)/a.amount
from table1 a left outer join table 1 b
on cast(right(a.quarter,1) as int) - cast(right(b.quarter),1) = 1|||change in the logic...
select a. quarter, a.amount, (a.amount-b.amount)/isnull(b.amount,1)
from table1 a left outer join table 1 b
on cast(right(a.quarter,1) as int) - cast(right(b.quarter),1) = 1|||Thank you so much for your reply! You saved me a lot of time and headache
for a project shortly due! :)
I made minor adjustments as below and it worked!
select a. quarter, a.amount, (a.amount-b.amount)/b.amount as diff
from table4 a left outer join table4 b
on cast(right(a.quarter,1)as int) - cast(right(b.quarter,1)as int) = 1
"Omnibuzz" wrote:

> untested.. but it should be in these lines. Hope this helps :)
> select a. quarter, a.amount, (a.amount-b.amount)/a.amount
> from table1 a left outer join table 1 b
> on cast(right(a.quarter,1) as int) - cast(right(b.quarter),1) = 1|||Better logic!
Thanks!
"Omnibuzz" wrote:

> change in the logic...
> select a. quarter, a.amount, (a.amount-b.amount)/isnull(b.amount,1)
> from table1 a left outer join table 1 b
> on cast(right(a.quarter,1) as int) - cast(right(b.quarter),1) = 1|||if you are looking no further, please mark the question as answered so that
the experts don't look at it as an unanswered question.|||Would you also know what is the best way to do this if the quarter and the
amount fields were in separate tables?
"Omnibuzz" wrote:

> if you are looking no further, please mark the question as answered so tha
t
> the experts don't look at it as an unanswered question.
>|||how will the quarter and amount fields be joined?
can you give an example with data?
"SK" wrote:
> Would you also know what is the best way to do this if the quarter and the
> amount fields were in separate tables?
>
> "Omnibuzz" wrote:
>|||This is what I had to get the two fields (quarter & amounts) that I needed
for calculations.
select SSO_DATES.QUARTER, SUM(SSO.AMOUNT) as AMOUNT
from SSO INNER JOIN SSO_DATES
ON SSO.SSO_ID=SSO_DATES.SSO_ID
where SSO.STATUS='8'
group by SSO_DATES.QUARTER
order by SSO_DATES.QUARTER
What would be the best way to combine/merge this with the query for
calculations given earlier?
Should I use a subquery of some sort?
"Omnibuzz" wrote:
> how will the quarter and amount fields be joined?
> can you give an example with data?
> "SK" wrote:
>|||This is one option in SQL Server 2000
select a. quarter, a.amount, (a.amount-b.amount)/isnull(b.amount,1)
FROM
(
select SSO_DATES.QUARTER, SUM(SSO.AMOUNT) as AMOUNT,
from SSO INNER JOIN SSO_DATES
ON SSO.SSO_ID=SSO_DATES.SSO_ID
where SSO.STATUS='8'
group by SSO_DATES.QUARTER
order by SSO_DATES.QUARTER
) AS A LEFT OUTER JOIN
(
select SSO_DATES.QUARTER, SUM(SSO.AMOUNT) as AMOUNT,
from SSO INNER JOIN SSO_DATES
ON SSO.SSO_ID=SSO_DATES.SSO_ID
where SSO.STATUS='8'
group by SSO_DATES.QUARTER
order by SSO_DATES.QUARTER
) AS B
on cast(right(a.quarter,1) as int) - cast(right(b.quarter),1) = 1
-- another option is to use a table variable like this
declare @.table1 table(QUARTER int, amount int)
insert into @.table
select SSO_DATES.QUARTER, SUM(SSO.AMOUNT) as AMOUNT,
from SSO INNER JOIN SSO_DATES
ON SSO.SSO_ID=SSO_DATES.SSO_ID
where SSO.STATUS='8'
group by SSO_DATES.QUARTER
order by SSO_DATES.QUARTER
Which is better depends on how your system is configured. whether it can
take more IO operations or memory intensive ops and also on the number of
rows returned by the subquery. The way I see it, there should only be at the
max 4 rows returned. So you can go for the first option.
If you are using SQL Server 2005, you can use CTE.
Both options given above are untested. written from home.
Hope this helps.