Difference between records in query

[Guest]

I am working on a database to track utility meter readings. My data is
stored in tbl_readings with the fields meternum, date, read. I need to
calculate the difference between the meter readings. I would like to first
sort the records, then assign an index and take the difference between
reading with index 1 and index – 1. I have worked out a solution but I think
it is very inefficient. I looked at all possible combinations of dates and
then ordered the difference between them. With a large record set, this will
probably be a problem. Any ideas.

Thanks
John

 

[Tom Ellison]

Dear JS:

The classic solution is to use a subquery to retrieve the previous meter
reading. If you'll post the SQL of a query that does everything else but
retrieve this previous reading, I'll propose an alteration to that which
does what you want.

Tom Ellison

 

[Guest]

My SQL use is very limited, so I don't really know how to start out. Here is
a query that simply gives the information in the table sorted by MeterNum,
and Read Date. I don't know how to assign an index after sorting the
records. If you had a subquery that I could link to with the previous
record, I would simply subtract the two meter readings for the difference.

SELECT tbl_Readings.MeterNum, tbl_Readings.ReadDate, tbl_Readings.Read
FROM tbl_Readings
ORDER BY tbl_Readings.MeterNum, tbl_Readings.ReadDate;

Thanks John

 

[Tom Ellison]

Dear JS:

Now, this is a pretty good start. I presume you'll want to see this for
only the most recent ReadDate, and for the ReadDate immediately previous to
that one. Would that be right?

SELECT MeterNum, ReadDate, Read
FROM tbl_Readings R
WHERE ReadDate = (
SELECT MAX(ReadDate)
FROM tblReadings R1
WHERE R1.MeterNum = R.MeterNum)
ORDER BY MeterNum

The above should show you only the most recent reading for each meter. Does
it? Well, that's a start.

Now for the previous reading date and reading.

SELECT MeterNum, ReadDate, Read,
(SELECT MAX(ReadDate)
FROM tblReadings R1
WHERE R1.MeterNum = R.MeterNum
AND R1.ReadDate < R.ReadDate)
AS PrevDate
FROM tbl_Readings R
WHERE ReadDate = (
SELECT MAX(ReadDate)
FROM tblReadings R1
WHERE R1.MeterNum = R.MeterNum)
ORDER BY MeterNum

Above, I've added just the date of the previous reading. I'll use that date
to find the reading on that previous reading date.

SELECT MeterNum, ReadDate, Read,
(SELECT MAX(ReadDate)
FROM tblReadings R1
WHERE R1.MeterNum = R.MeterNum
AND R1.ReadDate < R.ReadDate)
AS PrevDate,
(SELECT Read
FROM tblReadings R1
WHERE R1.MeterNum = R.MeterNum
AND R1.ReadDate =
(SELECT MAX(ReadDate)
FROM tblReadings R2
WHERE R2.MeterNum = R1.MeterNum
AND R2.ReadDate < R.ReadDate))
AS PrevRead
FROM tbl_Readings R
WHERE ReadDate = (
SELECT MAX(ReadDate)
FROM tblReadings R1
WHERE R1.MeterNum = R.MeterNum)
ORDER BY MeterNum

Access Jet occasionally has trouble with this. There is a work around if
this happens.

The above uses subqueries and aliasing. Read up on these subjects for a
starting understanding of it.

Please let me know if this helped.

Tom Ellison

 

[Guest]

I think I follow what is happening. Are the R and R1 the alias tables
created to make this query work? The data comes back with the difference
between the last two meter readings for each meter. Is there any way to get
the difference for all the readings? I would expect the first record
wouldn't work, as there wouldn't be a record before it, so it could be zero.
Would this use some kind of loop? Very Helpful thanks JS.

 

[Guest]

I think I fixed the issue. I removed the where statement at the bottom, and
it now works for all meter readings. I have alot to learn about sql, it
looks like I don't have to make 7 or 8 "design view" queries when it can be
done with 1 sql query. I will try to learn more about it. Thanks again.

 

[Tom Ellison]

Dear JS:

R, R1, and R2 are aliases. I would not say they are "alias tables" but
rather that they are independent instances of the same table. In other
words, "look at the same table without reference to any of the other
instances of that same table."

I did not provide a "difference between the last two meter redings" for each
meter. I provided the dates and meter readings. You certainly could
calculate the difference using what I gave.

Now, the concept of "the difference for all the readings" makes no sense to
me. Difference is "subtraction" and "subtraction" is a binary operation. A
difference is found between any two numbers, not a large set of numbers.
Please explain and give an example of what this means.

Reading the rest of what you say, I'm wondering if you want a "rolling
difference" between every consecutive pair of readings. Is this it?

There are no "loops" in SQL queries. But, if I understand you correctly,
the rolling difference could be done. I would simply exclude the first
reading from the list of readings, as there is no previous reading to that.

Did what I posted work well?

SELECT MeterNum, ReadDate, Read,
(SELECT MAX(ReadDate)
FROM tblReadings R1
WHERE R1.MeterNum = R.MeterNum
AND R1.ReadDate < R.ReadDate)
AS PrevDate,
(SELECT Read
FROM tblReadings R1
WHERE R1.MeterNum = R.MeterNum
AND R1.ReadDate =
(SELECT MAX(ReadDate)
FROM tblReadings R2
WHERE R2.MeterNum = R1.MeterNum
AND R2.ReadDate < R.ReadDate))
AS PrevRead
FROM tbl_Readings R
WHERE ReadDate > (
SELECT MIN(ReadDate)
FROM tblReadings R1
WHERE R1.MeterNum = R.MeterNum)
ORDER BY MeterNum, ReadDate

All I have done is to change the filter in the main part of the query so it
includes all the rows except the oldest.

I did not mention it before, but this presumes you never have two readings
for the same meter with the same ReadDate. That would create an ambiguous
situation and will probably cause an error when running the query, as it
would cause a subquery to return two values where only one is permitted.
For this reason, a constraint on your data to prevent that may be a good
idea, if it is not already in place. Whether this is appropriate may depend
on the overall design of the data, and is potentially a complex question
dealing with the original data design.

Tom Ellison

 

[Tom Ellison]

Dear JS:

You got that right! My post changed that portion so it filters only the
original row wihich has no preceding row. Pretty much the same thing. I
believe you're catching on!

Tom Ellison