Solved

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

Posted on 2004-08-26
3
389 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

746 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

12 Experts available now in Live!

Get 1:1 Help Now