variance between the values of two records, but same record set

[mikeS]

How can I calculate the difference between the value of
one field and the value of that same field in the next
record of the record set.
For example, I have a table with equip#, date,
meter_reading, and usage. Usage is just the increase in
the meter_reading from the previous record that has the
same equipment number.

 

[Albert D. Kallal]

You can certainly do this.

lets make outer query that would show the meter reading.


select id,MeterRead from tblReadings order by id

The above would show:

id MeterRead
12 1001
13 1020

etc. Now, lets add a 3rd collum that is the differen between each meterread


select id,MetterRead,
(select top 1 MeterRead from tblReadings as t
where t.MeterRead < tblReadings.MeterRead order by id desc) as MeterPrev,
(MeterRead - MeterPrew) as MeterDif
from tblReadings ordery by id

If meterReads are ever the same, then you need to change the order by clause
in the sub query to make sure the top 1 ONLY returns one record, else an
error will occur. You can use somting like (by adding more then one field to
the order by, then top 1 will return ONLY one value EVEN in the case where
the metter reading has not changed

Order by MeterRead DESC,id desc