CODEWITHSUNDEEP
oracle-databaseplsql
FirstTimer
FirstTimer

Reputation: 45

using lag () to perform calculation

I need help to calculate the consumption of a meter base on a current value taken each month

I have the following tables:

Address(PostCode int, Street varchar)
Meter(MeterID int, InstallationDate date, PostCode int)
Reading(ReadNo int, ReadDate date, ReadValue int, MeterID int, ReaderID int)

I tried the following:

Select ReadDate, 
       ReadValue AS CurrentValue, 
       lag(readvalue, 1, 0) OVER (ORDER BY READVALUE) AS PreviousValue, 
       readvalue - lag(readvalue, 1, 0) OVER (ORDER BY READVALUE) AS Consumption
 From Reading R, 
      Meter M, 
      Address A 
Where R.MeterID = M.MeterID
  AND A.PostCode = M.PostCode AND A.PostCode = '1005'
Order By ReadDate;

Problem occur when the meter of the address is being replace by a new meter, can anyone help me with that?

Upvotes: 0

Views: 100

Answers (2)

jim mcnamara
jim mcnamara

Reputation: 16379

Does your schema not relate meters (services) to premises? If you are using a commercial Customer Information System, all of them do that and require its use to generate bills. There is a table of services, each service of which lives at a premises. The mailing address may or may not be the same as the premises. The mailing address is associated with the customer, example: a restaurant chain has one main office and 20 restaurants. They get bills at the main office, so all of the mailing addresses are the same, the 20 physical addresses of the services are probably very different.

If your rolled your own schema for this you need to think through how utility billing actually works. Even if this is just a toy model.

I've done this for the past 30 years on lots of systems. They all work like the above.

Upvotes: 0

Ben
Ben

Reputation: 52853

I'm sure quite a lot of people are going to thank you!

By using ORDER BY READVALUE in the LAG you're calculating the next highest value (the default sort order is ascending) not the last taken one. This should be 

lag(readvalue, 1, 0) over (order by readdate desc)

to obtain the previously read value.

You don't have enough information in your address table to determine whether a meter is at the same address or not. There's no building number etc. So, the correct answer to your problem is social and unrelated to the database. If someone has two meters send out two bills.

However, assuming that the address table does have enough information in order to be able to uniquely identify an address, i.e. each postcode is a single building then you're already doing the correct thing, though I would re-organise your query as follows (and stop quoting integers).

select r.readdate
     , r.readvalue - lag(r.readvalue, 1, 0) over ( order by readdate desc )
  from address a
  join meter m 
    on a.postcode = m.postcode
  join reading r
    on m.meterid = r.meterid
 where a.postcode = 1005
 order by readdate desc

You're selecting every meter at a postcode then every reading for all of those meters. If a new meter got installed between readings you're fine.

It seems unlikely that this will work; there should be a final reading for the previous meter otherwise the readvalue will be higher than the lag of that value, which brings you back to the two bills scenario. Alternatively, you need a method of showing when a bill was "final" in the database.

To get the latest value simply wrap this all in a sub-select and select the top row:

select * from ( <above query> ) where rownum < 2

Upvotes: 1

Related Questions