Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    2

    Question 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).

    How can I subtract adjacent records in a query? Please help.
    Last edited by CrackToad; 07-29-02 at 15:56.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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. #3
    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
  •