Solved

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

Posted on 2004-08-26
3
393 Views
Last Modified: 2008-02-01
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.

0
Comment
Question by:keeper3
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 125 total points
ID: 11906737
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
 

Author Comment

by:keeper3
ID: 11906875
Thought it was easy, many thanks.
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11906876
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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now