List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:November 17 2009 6:35pm
Subject:Techniques for queries on a series?
View as plain text  
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  

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

Techniques for queries on a series?Jan Steinman17 Nov
  • Re: Techniques for queries on a series?Peter Brawley17 Nov
    • Re: Techniques for queries on a series?Jan Steinman17 Nov
  • with passing host variables to mySQLCharles Brown18 Nov