Which seems proper, mathematically, for this query?

Question

I'm writing a SQL query which calculates the average daily water usage for a utility company.

Usage reads come to us in the following format:

ReadBegin (start date of meter reading)
ReadEnd (end date of meter reading)
Volume (in Liters)

The meter reads have a ReadBegin and ReadEnd typically crossing month boundaries, for example, 3/31/2007 and 4/13/2007. Most months there is only a single read for the month, but occasionally there are two.

My question, say you have these reads in a given month:

3/1/2007 - 3/31/2007, average daily usage 100.10 L
3/31/2007 - 4/13/2007, average daily usage 39.4 L

What would be the proper way to factor in the average daily usage for 3/31, since here there are essentially two meter reads (and therefore two ranges used to calculate averages) used?

I was thinking that for March 1 - 30, I would use 100.10 (the ADU for the first read), and for March 31 I would use the average of the two averages, 100.10 + 39.4 / 2.

Answer 1

I believe that if you look closely at your dates columns you will find that the ReadBegin is always the same as the previous ReadEnd. That being the case, the correct way to arrive at averages is to assume that the read time is exactly midnight on either the ReadEnd(PM) or the ReadBegin (AM). While this is not the case in actuality, it will do for the averages. Actually, you may assume any time of reading, but the ReadEnd day must be assumed to end at the time of the reading, and the ReadBegin day must be assumed to be the next calendar date. By far the easiest assumption to implement is the midnight(PM) end of the ReadEnd day and the start of the NEXT day for the ReadBegin.

For SQL, where you are "examining" one record at a time, Your formula needs to be
ADU = Volume / (ReadEnd - ReadBegin)
Where the Begin/End dates are not the last day of the month, You will need to prorate to arrive at a meaningful monthly ADU.

With the data given, the March ADU would be
(30(Mar1-Mar31ADU) + 1(Feb?-Mar1ADU)) / 31
The April ADU would be
(13(Mar31-Apr13ADU) + 17(Apr13-?ADU)) / 30

There are more sophisticated methods you can use on long-term data to get a more accurate idea of ADU, but they require a great deal more math and code.

Answer 2

I cant believe the meter readeer came two times aon March 31. I believe he came on March 31st for the for the 2/1/2007 to 3/31/200 7 reading. Then hecame againn on April 13 for the 4/1/2007 to 4/13/2007 reading.

The 3/31/2007should not appear twice. It would be charging the customer incorrectly to avearge the two ADUs as you propose.

Answer 3

More than likely the utility use a concept called " degree days" when they computed "average usage"...on hotter/drier days more water would likely be used...this is certainly used by utilities which provide gas and electricity in those months when a rate change happens.