Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-08-26
3
Medium Priority
?
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 500 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

730 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