Cumulative Difference

Question

I have a table

Meter_Reading

MeterID    |    Reading     |     DateRead     |
1               10                1-Jan-2012
1               20                2-Feb-2012
1               30                1-Mar-2012
1               60                2-Apr-2012
1               80                1-May-2012

The reading is a cumulative value where i would need to calculate the difference from the previous month and the current month.

Could you help me figure out how to generate a view where i can see the consumption (previous month reading - current month reading) for each month?

I had tried the between function:

select address, reading as Consumption, dateread
from ServiceAddress, reading, meter
where address like '53 Drip Drive%'
and dateread 
between (to_date('01-JAN-2012','DD-MON-YYYY')) and (to_date('30-SEP-2012', 'DD-MON-YYYY'))
and serviceaddress.serviceaddid = meter.serviceaddid and meter.meterid = reading.meterid;

but all i got was the readings for each month not the difference.

How could I make it list the monthly consumption?

Answer 1

Try with analytic functions. Something like this should do the trick:

SELECT meterid, dateread, 
    reading - LAG(reading, 1, 0) OVER(PARTITION BY meterid ORDER BY dateread)
FROM meter_reading 

Answer 2

You can use the LAG function to get the reading for the prior month. The query you posted references three tables-- ServiceAddress, Reading, and Meter none of which are the Meter_Reading table you posted the structure and data for. I'll ignore the query you posted since I'm not sure what the data in those tables looks like and focus on the Meter_Reading table that you posted data for

SELECT MeterID, 
       DateRead,
       Reading,
       PriorReading,
       Reading - PriorReading AmountUsed
  FROM (SELECT MeterID,
               DateRead,
               Reading,
               nvl(lag(Reading) over (partition by MeterID 
                                          order by DateRead), 
                   0) PriorReading
          FROM meter_reading)

I assume that if there is no prior reading that you want to assume that the prior reading was 0