Jan,
> I often need a pattern where one record refers to the one "before"
it, based on the order of some field.
Some ideas under "Sequences" at
http://www.artfulsoftware.com/infotree/queries.php.
PB
----
Jan Steinman wrote:
I often need a pattern where one record refers to the one "before" it,
based on the order of some field.
For example, a vehicle log, in which the field "odometer" is the
current odometer reading upon an event, such as a trip completion,
fueling, maintenance, etc. Very often one wants to calculate the
difference in odometer readings, which means accessing two consecutive
records, when ordered by odometer. I currently put both readings in
one record, which seems an unnecessary de-normalization and extra work
in data entry.
Another example: an amortization database, where the value of the loan
principle depends on the payment, interest rate, but also the previous
record's principle. Someone makes a payment on a loan, which needs to
be entered along with the declining balance, but that depends on the
balance of the previous record.
Quite often, I see this pattern in time series data. Data is logged
and time-stamped, and many queries depend on the difference in
time-stamps between two consecutive records. For example, milk
production records: with milk goats, if milking is early or late, the
amount of milk is lower or higher. I need to do an analysis of
short-term milk production, which means daily production needs to be
normalized for variations in time, which means I need to refer to time
and volume deltas from two consecutive records, ordered by time.
Are there some good techniques for dealing with this common pattern in
SQL? Or do I need to do it all with two queries and a programming
language?
Pointers to good web references are welcome. I have googled quite a
bit, and haven't turned up anything apropos.
Thanks for whatever insight you can offer!
:::: A virus has marked this email as being virus-free! ::::
:::: Jan Steinman http://www.VeggieVanGogh.com ::::
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.425 / Virus Database: 270.14.69/2508 - Release Date: 11/17/09 07:40:00
