how i get the difference between two records?

W

weiyn

i record the total from a flowmeter to a table hour by hour.how i get the
difference of flow between per hour.thanks!
 
B

Beetle

Typically with the DateDiff function.

For a more specific answer, you'll need to post a more specific question.
 
M

Michael Gramelspacher

i record the total from a flowmeter to a table hour by hour.how i get the
difference of flow between per hour.thanks!
Click to expand...

maybe something like this:

SELECT a.meter_serial_nbr,
a.reading_date,
a.meter_value,
a.meter_value - a1.meter_value AS change_amt
FROM MeterReadings AS a
LEFT JOIN MeterReadings AS a1
ON a.meter_serial_nbr = a1.meter_serial_nbr
WHERE a1.reading_date = NZ((SELECT MAX(a2.reading_date)
FROM MeterReadings AS a2
WHERE a2.meter_serial_nbr = a.meter_serial_nbr
AND a2.reading_date < a.reading_date),
a.reading_date);

meter_serial_nbr reading_date meter_value change_amt
123 12/31/2007 7:00:00 AM 234.5 0
123 12/31/2007 8:00:00 AM 253.6 19.1
123 12/31/2007 9:00:00 AM 225.2 -28.4
456 12/31/2007 7:00:00 AM 311.4 0
456 12/31/2007 8:00:00 AM 320.5 9.1
 
W

weiyn

good!Thank you.i will try it.

Michael Gramelspacher said:
maybe something like this:

SELECT a.meter_serial_nbr,
a.reading_date,
a.meter_value,
a.meter_value - a1.meter_value AS change_amt
FROM MeterReadings AS a
LEFT JOIN MeterReadings AS a1
ON a.meter_serial_nbr = a1.meter_serial_nbr
WHERE a1.reading_date = NZ((SELECT MAX(a2.reading_date)
FROM MeterReadings AS a2
WHERE a2.meter_serial_nbr =
a.meter_serial_nbr
AND a2.reading_date < a.reading_date),
a.reading_date);

meter_serial_nbr reading_date meter_value change_amt
123 12/31/2007 7:00:00 AM 234.5 0
123 12/31/2007 8:00:00 AM 253.6 19.1
123 12/31/2007 9:00:00 AM 225.2 -28.4
456 12/31/2007 7:00:00 AM 311.4 0
456 12/31/2007 8:00:00 AM 320.5 9.1
Click to expand...
 
Top