CODEWITHSUNDEEP
sqloracle-database
Martin Ocando
Martin Ocando

Reputation: 924

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

Upvotes: 0

Views: 125

Answers (1)

user5683823
user5683823

Reputation:

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.

Upvotes: 1

Related Questions