I'm not sure if this is the sort of thing you are looking for but the
following is the SQL for a query I wrote many years ago based on table
Readings with columns
ReadingDate, ReadingType (gas, electricity etc) and Reading:
SELECT R1.ReadingType, R1.ReadingDate,
R1.ReadingDate -
(SELECT MAX(R2.ReadingDate)
FROM Readings As R2
WHERE R2.ReadingDate < R1.ReadingDate
AND ReadingType = R1.ReadingType) AS DaysSinceLastReading,
R1.Reading -
(SELECT MAX(R3.Reading)
FROM Readings As R3
WHERE R3.ReadingDate < R1.ReadingDate
AND ReadingType = R1.ReadingType) AS [Usage]
FROM Readings AS R1
WHERE R1.ReadingDate >
(SELECT MIN(R4.ReadingDate)
FROM Readings As R4
WHERE ReadingType = R1.ReadingType)
ORDER BY R1.ReadingType, R1.ReadingDate;
If you want the difference between each reading for each eqptId then if you
change the SQL statement so that the references to Readings are to your table
name, those to ReadingType are to eqptId and those to Reading are to [monthly
reading] it should work with your data. It also gives you the time in days
between each reading as well as the difference in units between the readings.
If you want to show the readings themselves, including the initial reading
for each ReadingType change it to:
SELECT R1.ReadingType, R1.ReadingDate,
R1.Reading, R1.ReadingDate -
(SELECT MAX(R2.ReadingDate)
FROM Readings As R2
WHERE R2.ReadingDate < R1.ReadingDate
AND ReadingType = R1.ReadingType) AS DaysSinceLastReading,
R1.Reading -
(SELECT MAX(R3.Reading)
FROM Readings As R3
WHERE R3.ReadingDate < R1.ReadingDate
AND ReadingType = R1.ReadingType) AS [Usage]
FROM Readings AS R1
ORDER BY R1.ReadingType, R1.ReadingDate;
Ken Sheridan
Stafford, England
