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

  • Thread starter Thread starter mikeS
  • Start date Start date
M

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.
 
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
 
Back
Top