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

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.

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).

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.

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.

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.

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.

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



-- 
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