Re: obtain the difference between successive rows

On 20/10/2012 17:02, Berend Tober wrote:
> Thalis Kalfigkopoulos wrote:
>> SELECT current_meter_reading - lag(current_meter_reading) OVER(ORDER
>> BY current_reading_date) AS kWh_diff, extract('days' FROM
>> current_reading_date - lag(current_reading_date) OVER(ORDER BY
>> current_reading_date)) as num_service_days FROM mytable;
>
> How would you get the previous reading (and perhaps the previous read
> date) to also appear on the same output row? The sample table with the
> subtraction I showed for illustration is literally what is printed on
> the bill ... they are not just presenting the quantity used and the
> number of days, but actually the dates and meter readings used to do the
> arithmetic.

Just include them in the SELECT:

SELECT
lag(current_meter_reading) OVER(ORDER BY current_reading_date) AS
kWh_prev,
current_meter_reading - lag(current_meter_reading) OVER(ORDER BY
current_reading_date) AS kWh_diff,

(...etc...)

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie