Martin Ocando Martin Ocando - 2 months ago 15
SQL Question

Running total and reading reset

I have a table that stores hourmeter reading for our machines. The readings are stored as a running total, so every read, that occurs several times a month, is an accumulated total.

The thing is, sometimes the meter breaks, and they are replaced. In that case, the reading type changes from ACTUAL to RESET, and the reading is reset to the new value, breaking the running total.

I have a query that gets the MAX of the meter reading per month, so I can get the last reading, so I can join with another table in Tableau to make a visualization comparing maintenance costs with operating hours.

My current query is like this:

select assetnum,
to_date(to_char(readingdate, 'MM/')||'01/'||to_char(readingdate, 'YYYY'), 'MM/DD/YYYY') reading_date,
max(reading) month_reading
from meterreading
group by assetnum, to_char(readingdate, 'MM/')||'01/'||to_char(readingdate, 'YYYY')
order by to_date(to_char(readingdate, 'MM/')||'01/'||to_char(readingdate, 'YYYY'), 'MM/DD/YYYY')


This returns a table like this:

Assetnum readingdate month_reading
8021 01/01/2016 3500
8021 02/01/2016 4200
8021 03/01/2016 5100
8021 04/01/2016 5900
8021 05/01/2016 6300
8021 06/01/2016 200 <-- meter was reset
8021 07/01/2016 350
8021 08/01/2016 403


So, I've envisioned two ways of fixing this:

1) I find a way to add the last reading to the current one, if the meter gets reset, or

2) Remove the accumulation, and on each reading simply get the current month reading, so it doesn't matter if it gets reset

I'm leaning more towards option 2, but I'd like to hear you opinion.

BTW, as I mentioned above, the source table have a field called readingtype, which says ACTUAL if is a regular reading, or RESET if the hourmeter was replaced.

Thanks for the help

UPDATE!!!

What I'd like to achive is this:

Assetnum readingdate month_reading
8021 01/01/2016 3500
8021 02/01/2016 4200
8021 03/01/2016 5100
8021 04/01/2016 5900
8021 05/01/2016 6300
8021 06/01/2016 6500 <-- add the current to the previous
8021 07/01/2016 6850
8021 08/01/2016 7253


Or this:

Assetnum readingdate month_reading
8021 01/01/2016 3500
8021 02/01/2016 700 <-- get the monthly value, not accumulated
8021 03/01/2016 900
8021 04/01/2016 800
8021 05/01/2016 400
8021 06/01/2016 200 <-- meter was reset, so no matter
8021 07/01/2016 350
8021 08/01/2016 403


Hope is clearer now

Answer

Assuming the RESET readingtype value is attached only to the first full month after a reset, the second approach is easy to implement. To get the "monthly usage" you would use

case readingtype when 'RESET' then month_reading
                 else month_reading - lag(month_reading) 
                                      over (partition by assetnum order by readingdate)
end as monthly_usage

in your SELECT clause.

In any case, even without the help of readingtype, you can rewrite the case expression to test for month_reading < lag(month_reading) over ... (which is the marker of a RESET); do take advantage of readingtype if it's already there though.

ADDED: Apparently, the very first monthly reading for each asset is not marked "RESET" (which makes it different from all the other "first" readings). This causes a problem, because for the very first reading for each asset, lag(...) is NULL (there is no previous reading) and therefore the difference is NULL as well.

This can be fixed. Instead of subtracting lag(...) over (...), one needs to subtract

nvl( lag(...) over (...), 0 )

That is, subtract the lagging value UNLESS it is null, in which case subtract 0.