Hey everyone,
I have a small project I would like to share with you all for some ideas...
Monthly, i'm receiving electricity usage meter totals along with the date the meters were read for a whole range of different meter numbers. ie.
Meter
ID
MeterNumber
MeterTotal
DateRead
The meter usage totals are recorded straight from the meters outside peoples houses, so as you probably have guessed, the data i receive each month is a progressive total.
The problem:
Generate a report each month that shows only the months usage.
Is there an easy way do this with a query? I am a programmer, and can think of a lot of different ways to do this using scripting, however i would like to know if this can be done with only SQL.
The problem looks simple at first, however if you look at it closely there are a couple of issues that need to be solved:
1. Subtracting the previous months meter reading from the current months to get the monthly total - can't seem to do with an sql statement.
2. Handling not having a previous month reading (ie, first readings ever)
3. Client could send 200 readings in month one, then expand their business and send 230 readings the next month. Therefore 200 of the meters will need a subtraction, however 30 of the meters will not.
4. Meter numbers could be reset to 0000000 (after they cycle through their lifetimes for example - which i'm told is not too worry about because the digits will last my lifetime).
My immediate thoughts on this problem is that the meter usage total is an example of storing a calculated field. Instead we should calculate the months total upon receiving the data and insert that into the database instead, however this will require programming effort.
Anybody have any ideas if this can be done with sql?
Brett Birkett B.Comp
Systems Analyst
I have a small project I would like to share with you all for some ideas...
Monthly, i'm receiving electricity usage meter totals along with the date the meters were read for a whole range of different meter numbers. ie.
Meter
ID
MeterNumber
MeterTotal
DateRead
The meter usage totals are recorded straight from the meters outside peoples houses, so as you probably have guessed, the data i receive each month is a progressive total.
The problem:
Generate a report each month that shows only the months usage.
Is there an easy way do this with a query? I am a programmer, and can think of a lot of different ways to do this using scripting, however i would like to know if this can be done with only SQL.
The problem looks simple at first, however if you look at it closely there are a couple of issues that need to be solved:
1. Subtracting the previous months meter reading from the current months to get the monthly total - can't seem to do with an sql statement.
2. Handling not having a previous month reading (ie, first readings ever)
3. Client could send 200 readings in month one, then expand their business and send 230 readings the next month. Therefore 200 of the meters will need a subtraction, however 30 of the meters will not.
4. Meter numbers could be reset to 0000000 (after they cycle through their lifetimes for example - which i'm told is not too worry about because the digits will last my lifetime).
My immediate thoughts on this problem is that the meter usage total is an example of storing a calculated field. Instead we should calculate the months total upon receiving the data and insert that into the database instead, however this will require programming effort.
Anybody have any ideas if this can be done with sql?
Brett Birkett B.Comp
Systems Analyst
