Inner Join the same table to compare record against the previous record

I am working with readings from a meter (like an electric meter), where the readings are on a totalizer, so to get the amount used between readings, you subtract the earlier reading from the later reading.

So, I am trying to do a sql statement that joins in the immediate previous reading so you can subtract it.

The table (meter_data) with the essential fields and example data...

reading_id   read_date     read_time     reading
-----------    -----------      -----------      ---------
    1            2004-08-25    4:00:00         100
    2            2004-08-26    5:00:00         150
    3            2004-08-26    11:00:00       170

Right now, I have something like...

SELECT m1.read_date,m1.read_time,m1.reading - m2.reading as amount_used

FROM meter_data m1 inner join meter_data m2 on mm1.read_date >= mm2.read_date and mm1.read_time > mm2.read_time

This gets the records setup, but it obviously doesn't limit it to the "immediate previous reading", and will get any reading previously, so I end up with more records than I want.

The results I would be looking for would be...

reading_id   read_date     read_time     amount_used
-----------    -----------      -----------      ---------
    3            2004-08-26    11:00:00         20
    2            2004-08-26    5:00:00          50

This seems like it shouldn't be hard, but my skills are slow today...

Thanks.

keeper3Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
snoyes_jwConnect With a Mentor Commented:
If you tack on
GROUP BY m2.read_date, m2.read_time
it seems to work, although you'll probably want to check me on that.
0
 
keeper3Author Commented:
Thought it was easy, many thanks.
0
 
snoyes_jwCommented:
On further study, that doesn't seem to work so well.  If reading_id is always increment by 1, you could join on m1.reading_id = m2.reading_id + 1, but I don't much like that solution.  

How about this:

SELECT
    m1.read_date, m1.read_time, m1.reading - m2.reading as amount_used
FROM
    meter m1, meter m2
WHERE
    (m1.read_date > m2.read_date OR (m1.read_date = m2.read_date AND m1.read_time > m2.read_time))
GROUP BY
    m2.read_date, m2.read_time;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.