# Thread: Subtract previous record from current one

1. Registered User
Join Date
Jul 2002
Posts
2

## Unanswered: Subtract previous record from current one

Hello all,
I have a table that holds tool readings for certain dates. The readings determine usage and are integer values. Tool readings are noted every few days and inputed into the table all at once. The table is structured as such:

Fields: date_measured; tool_1; tool_2; tool_3; etc...

Data looks like this in the table:

date_measured; tool_1; tool_2; tool_3
7/15/02; 7794; 865; 22304
7/19/02; 7836; 910; 22376
7/22/02; 7887; 967; 22414
and so on...

I have a query that sorts the table in descending order by date. I need to determine the difference in dates and tool readings in adjacent records. For instance: 7/22/02 - 7/19/02 = 3 days; 7887 - 7836 = 51 (tool_1). I can use this data to determine average daily usage (51 divided by 3 days = 17 per day).

Last edited by CrackToad; 07-29-02 at 15:56.

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
the general strategy for adjacent records is a self-join, joining each row to the "highest of the lesser" rows (if you know what i mean)

something like this --
Code:
```select A.date_measured
, A.tool_1, A.tool_2, A.tool_3
, B.date_measured
, B.tool_1, B.tool_2, B.tool_3
, (B.date_measured - A.date_measured)
as days_elapsed
, (B.tool_1 - A.tool_1)
as tool1_usage
, (B.tool_2 - A.tool_2)
as tool2_usage
, (B.tool_3 - A.tool_3)
as tool3_usage
from yourtable A
, yourtable B
where B.date_measured
= ( select max(date_measured)
from yourtable
where date_measured < A.date_measured )```
caution: not tested

rudy
http://rudy.ca/

3. Registered User
Join Date
Jul 2002
Posts
2
Thanks, that did the trick....

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•