how to subtract value at previous row from current row

Hi all,

I need help writing a query that will subtract the values of 2 rows from the same column to display in the result set. Some background information: a table has a sales column that keeps track of sales by the minute, but this is done in a cumulative manner, i.e, sales at row 3(minute 3) = sales recorded @. minute 2 plus sales @. minute 3. Therefor to get the actual sale at min 3, i would have subtract value at row 2 from row 3. make sense? it sounds very easy but I am having a hard time refering back to the previous row and am dealing with more than 1000 rows. i thought about doing a self join on the table but could not get it to do what i want.

would appreciate any help i can get. thanks

Well, here's one methodology - but it is a bit dangerous if you ever delete records from this table. You will need to add a row identifying column (an auto incrementing number) - in this SQL statement I'm presuming it has a increment of 1.

SELECT (TableA.RowNumValue - TableB.RowNumValue) AS Val

FROM

YourTable TableA

INNER JOIN

YourTable TableB

ON

TableB.ID = TableA.ID - 1

This joins the table on itself, matching the previous record based on the autonumber ID (simply called "ID" here).

Once again, if you ever delete records this methodology won't be accurate. Of course, since you are entering the data "cumulatively", deleting data would be a problem for you anyway.

As a side question: why is it necessary to save the data in a cumulative manner? A database is designed for storage and aggregation - you'll save yourself a lot of headache if you simply store the data point as is, and sum the records up in a sql query.

--
Tony Alicea
http://www.theabstractionpoint.com
clarity of mind and creativity in application software development...

|||

Hi Tony,

Thank you so much for your time and input. I will try the self join again based on the method you showed me. The fisrt time I did, I used another field based on time and tried to manipulate but it didn't work....but your method makes sense
And I agree with you, it really doesn't help at all that the data is stored in this manner. Unfortunately, I have limited say in the design of it and this will probably be the last time I use it. But you are absolutely right.

Hey, thanks again.|||

Ah, trying to query a data structure that you didn't design. I've been there. Well, hope it works. If it does don't forget to mark the thread as answered to close it out.

Let us know how it goes!