H
Herluf
Fra: "Ken Sheridan" <[email protected]>
Emne: RE: I have a table that I need to subtract a reading from the prev. da
Dato: 18. januar 2009 19:56
As well as not storing the results of the computation, which, as Wayne
points
out, is not good design, you also appear to have separate columns for
different readings on the same day. Again this is bad design; its what's
known as 'encoding data as column headings'. A fundamental principle of the
database relational model is that data is stored as values at column
positions in tables and in no other way - its called the 'information
principle'. Assuming that the different columns represent readings of a
different type, or from different instruments, the correct way to handle
this
is to have a single column for all readings and a column such as ReadingType
or Instrument to differentiate between .
Here's a query which I produced some years ago in response to a similar
question elsewhere:
SELECT R1.ReadingType, R1.ReadingDate, R1.Reading,
(SELECT MAX(R2.Reading)
FROM Readings As R2
WHERE R2.ReadingDate < R1.ReadingDate
AND ReadingType = R1.ReadingType)
AS PreviousReading,
R1.Reading - PreviousReading AS Usage
FROM Readings AS R1
ORDER BY R1.ReadingType, R1.ReadingDate DESC;
The subquery returns the previous reading for the reading type, and is then
subtracted from the current reading to give the usage. So for a table
Readings:
ReadingDate...ReadingType...Reading
01/05/2004...Electricity.......70
01/05/2004...Gas..................50
01/04/2004...Electricity.......50
01/04/2004...Gas..................40
01/03/2004...Electricity.......35
01/03/2004...Gas..................25
01/02/2004...Electricity.......10
01/02/2004...Gas....................5
01/01/2004...Electricity.........2
01/01/2004 Gas....................1
The query would return:
ReadingType...ReadingDate...Reading...PreviousReading...Usage
Electricity.......01/05/2004...70............50..........................20
Electricity.......01/04/2004...50............35..........................15
Electricity.......01/03/2004...35............10..........................25
Electricity.......01/02/2004...10..............2.............................8
Electricity.......01/01/2004.....2
Gas..................01/05/2004...50............40..........................10
Gas..................01/04/2004...40............25..........................15
Gas..................01/03/2004...25..............5...........................20
Gas..................01/02/2004.....5..............1.............................4
Gas..................01/01/2004.....1
The above query assumes that the readings per reading type will always
increase over time, i.e. the meter will not be reset, but this might no be
the case of course as it might be necessary to replace a meter. You can
cater for this with the following query, which extends the above by the
introduction of a further subquery to restrict the first subquery to the
reading for the previous date per reading type, rather than the previous
highest reading:
SELECT R1.ReadingType, R1.ReadingDate, R1.Reading,
(SELECT MAX(R2.Reading)
FROM Readings As R2
WHERE ReadingType = R1.ReadingType
AND R2.ReadingDate =
(SELECT MAX(ReadingDate)
FROM Readings AS R3
WHERE R3.ReadingType = R1.ReadingType
AND R3.ReadingDate < R1.ReadingDate))
AS PreviousReading,
R1.Reading - PreviousReading AS Usage
FROM Readings AS R1
ORDER BY R1.ReadingType, R1.ReadingDate DESC;
If a meter is reset to a new initial reading then there will be a spurious,
and probably negative, usage returned of course as the previous reading on
the old meter will almost certainly be higher than the first reading on the
new meter unless this is set to match the final reading on the old meter.
If
you want to handle this possibility then add an Adjustment column to the
Readings table, giving it a DefaultValue property of zero, so that a
corrective value could be entered into the table if necessary. The usage
would then be:
(Reading – PreviousReading) + Adjustment
Ken Sheridan
Stafford, England
Emne: RE: I have a table that I need to subtract a reading from the prev. da
Dato: 18. januar 2009 19:56
As well as not storing the results of the computation, which, as Wayne
points
out, is not good design, you also appear to have separate columns for
different readings on the same day. Again this is bad design; its what's
known as 'encoding data as column headings'. A fundamental principle of the
database relational model is that data is stored as values at column
positions in tables and in no other way - its called the 'information
principle'. Assuming that the different columns represent readings of a
different type, or from different instruments, the correct way to handle
this
is to have a single column for all readings and a column such as ReadingType
or Instrument to differentiate between .
Here's a query which I produced some years ago in response to a similar
question elsewhere:
SELECT R1.ReadingType, R1.ReadingDate, R1.Reading,
(SELECT MAX(R2.Reading)
FROM Readings As R2
WHERE R2.ReadingDate < R1.ReadingDate
AND ReadingType = R1.ReadingType)
AS PreviousReading,
R1.Reading - PreviousReading AS Usage
FROM Readings AS R1
ORDER BY R1.ReadingType, R1.ReadingDate DESC;
The subquery returns the previous reading for the reading type, and is then
subtracted from the current reading to give the usage. So for a table
Readings:
ReadingDate...ReadingType...Reading
01/05/2004...Electricity.......70
01/05/2004...Gas..................50
01/04/2004...Electricity.......50
01/04/2004...Gas..................40
01/03/2004...Electricity.......35
01/03/2004...Gas..................25
01/02/2004...Electricity.......10
01/02/2004...Gas....................5
01/01/2004...Electricity.........2
01/01/2004 Gas....................1
The query would return:
ReadingType...ReadingDate...Reading...PreviousReading...Usage
Electricity.......01/05/2004...70............50..........................20
Electricity.......01/04/2004...50............35..........................15
Electricity.......01/03/2004...35............10..........................25
Electricity.......01/02/2004...10..............2.............................8
Electricity.......01/01/2004.....2
Gas..................01/05/2004...50............40..........................10
Gas..................01/04/2004...40............25..........................15
Gas..................01/03/2004...25..............5...........................20
Gas..................01/02/2004.....5..............1.............................4
Gas..................01/01/2004.....1
The above query assumes that the readings per reading type will always
increase over time, i.e. the meter will not be reset, but this might no be
the case of course as it might be necessary to replace a meter. You can
cater for this with the following query, which extends the above by the
introduction of a further subquery to restrict the first subquery to the
reading for the previous date per reading type, rather than the previous
highest reading:
SELECT R1.ReadingType, R1.ReadingDate, R1.Reading,
(SELECT MAX(R2.Reading)
FROM Readings As R2
WHERE ReadingType = R1.ReadingType
AND R2.ReadingDate =
(SELECT MAX(ReadingDate)
FROM Readings AS R3
WHERE R3.ReadingType = R1.ReadingType
AND R3.ReadingDate < R1.ReadingDate))
AS PreviousReading,
R1.Reading - PreviousReading AS Usage
FROM Readings AS R1
ORDER BY R1.ReadingType, R1.ReadingDate DESC;
If a meter is reset to a new initial reading then there will be a spurious,
and probably negative, usage returned of course as the previous reading on
the old meter will almost certainly be higher than the first reading on the
new meter unless this is set to match the final reading on the old meter.
If
you want to handle this possibility then add an Adjustment column to the
Readings table, giving it a DefaultValue property of zero, so that a
corrective value could be entered into the table if necessary. The usage
would then be:
(Reading – PreviousReading) + Adjustment
Ken Sheridan
Stafford, England