still no progress

[desertdirk]

I have been working on this for a while and still get nowhere. I want to
retrieve data from the previous month, and subtract it from similar
data for the current month.

more specifically, these are meter readings; each meter being read once
each month. How do I get Novembers readings, input Decembers readings,
subtract them and get an amount of usage?
Thanks

 

[Nikos Yannacopoulos]

Make a query that reads the previous month's readings
(meter_ID and reading)and a second similar one that reads
the current month's.
Then make a third query, add the previous two and join
them on the meter_ID field. Select the meter_ID field for
output, the two readings if you want them, and add a
calculated field to subtract the two.

HTH,
Nikos

 

[Tim Ferguson]

more specifically, these are meter readings; each meter being read once
each month. How do I get Novembers readings, input Decembers readings,
subtract them and get an amount of usage?

These queries are always based on horrendous SQL -- sometimes the nested-
querydef solution is the most practical even if not the fastest.

You probably need something like (not tested!!):-

SELECT a.MeterNumber,
a.DtRead,
a.ReadValue - b.ReadValue AS Difference
FROM Readings AS a, Readings AS b
WHERE a.MeterNumber = b.MeterNumber
AND b.DtRead = (
SELECT MAX(c.DtRead)
FROM Readings AS c
WHERE a.MeterNumber = c.MeterNumber
AND a.DtRead > c.DtRead
)
SORT BY a.MeterNumber, a.DtRead


I think there is a way of sqeezing out one of the table references, but
it's the end of a long day here... There are loads of SQL gurus on
m.p.a.adpsqlserver

Hope that helps a bit


Tim F