Re: [libreoffice-users] Other SQL question as it affects Base

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

--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted