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