Mark Stanton wrote:
Ok, this is, then, quite an interesting idea.
The problem is that you've got nothing reliable in your data to relate one date to the
next (by the looks of it). You're only taking readings at roughly monthly intervals.
Once a month, I get a bill from the local power company that contains the following data: the date the meter was read the previous billing cycle, its reading, the date the was read for this billing cycle, its reading, and the amount due. I would think this is very reliable: they want to get paid. And with bill in hand, it is not very difficult to enter the data into the table. If the accounting people do not do this on a regular basis, they are not likely to have a job. For an individual, it is all about diligence. Use it, and the results will be accurate and reliable. Without, your right, the results are useless. [Garbage IN Garbage Out.]
A good, purely SQL, solution to this would be to code a stored procedure that is
guaranteed to hand out successive numbers, and then you can use that to do the
arithmetic you've done already. You'd want another table that stores the current value
of your counter, and your entry data process would get the next number for your next
entry and update the table. You could do it by finding the maximum number already
allocated in your table and add one (or whatever).
This is far more complex than what I have created.
If you're prepared to bet the ranch on having one reading every month without fail, you
could find the lowest date and then generate an integer singly increasing index on
month's since first date. That'd be two more SQL selections.
I agree about databases in general of this type. But it seems to me that not having accurate data in is a problem that should be handled in another way. The query is designed for using available data to provide answer to the person who uses it. It can only give as good information as what is given it. I'm very diligent when it comes to data like this. I'm well aware of the phrase GIGO. I make sure that correct information is entered when it needs to be entered. (I don't put garbage in.) So, I don't get garbage out.
The problem with what you've done is that there's no guarantee that it'll continue to
work. It probably will, by the looks of it, but it's only a fortunate side effect of
something that is not guaranteed.
Strange you should write this. The query matches algebraic equations. I'm just not lucky: I am using the laws of algebra. It will always work. Yes, the problem is in the accuracy of the entered data. Diligence is the key. Addendum: While I have not added it yet, I kept track of the probably amount for this billing cycle using today's date, today's reading, the average KWH usage per day, the number of days, and the cost per KWH for the previous billing cycle. I update this more than once a week.
In a business environment you'd get fired for something like that, and quite rightly!
In an amateur environment, you're getting away with it, which might be good enough for
you.
How wrong you are! I too have some business experience. Mine proves you wrong. I used this process on paper during the years 1981 and 1982. During each year. I used four columns: week ending date last year, sales for the week ending on the date, week ending date this year, and sales for the week ending on the date. In my dry cleaning store, the week ended on Mondays. The first record had data for 1980 (by another manager) and 1981. The second had data for 1981 and 1982. The first proved that I was doing quite well as compared to the previous manager (1981 vs 1980). I knew this by mid summer, but the home office (in another city) did not know until about 3 months later. (There was a 37.5% increase in sales. That was nice.) The second indicated a drop in sales (the Regan recession). I saw that a couple of months before it was noticed in the home office. I knew what 4 other managers in the Baton Rouge area did not know because I had the data.
Why did the owner of the store visit my store to see my data every time he was in Baton Rogue? I made sure the data was always kept up to date. You see, you are right. The question is: How valid is the data that is being used? I made sure my data was valid. This is why rather than being fired for doing this, I was later moved to Lake Charles to take over a much larger store that included a laundry plant. I was even given the job of training new managers, but that required more than I could handle.
The next alternative is pulling the data in date order (yes, I know it's almost
certainly in date order already, but if you don't make sure then you can never enter
historical data [you're sure to miss entry for some reason some day]), and then run
through the table doing the sum between last month's data and this month's.
The bills I get makes entering historical data quite possible since each bill contains the present date read and its reading as well as the previous date read and its reading. This permits comparing two rows of the table for the dates and readings. Besides, if data were not entered for a month or more, the bills can be ordered by the month of the bill with the oldest on top. Then it is just a matter of checking the previous month's data against that row in the table and then entering this month's data.
Of course, if you do ever enter data out of order the first strategy here won't work,
or you'd have to manually reallocate your sequence numbers.
Regards Mark
--Dan
![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}