Techniques for queries on a series?

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Techniques for queries on a series?

--------------070209090909000906020606
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------070209090909000906020606--

Re: Techniques for queries on a series?

On 17 Nov 09, at 10:41, Peter Brawley wrote:

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

Thanks, Peter! What a marvellous resource!


:::: You know what? What makes our economy grow is energy. And
Americans are used to going to the gas tank, and when they put that
hose in their, uh, tank, and when I do it, I wanna get gas out of it.
And when I turn the light switch on, I want the lights to go on, and I
don't want somebody to tell me I gotta change my way of living to
satisfy them. Because this is America, and this is something we've
worked our way into, and the American people are entitled to it, and
if we're going improve our standard of living, you have to consume
more energy. -- Senator Chuck Grassley (R-IA) ::::
:::: Jan Steinman, http://www.VeggieVanGogh.com ::::




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

with passing host variables to mySQL

Can someone help me. I am trying to make calls to Mysql from VBscript via=
=3D =3D20ASP But I'm experiencing difficulties with passing host variable=
s to my SQL=3D20 See example below


isbn =3D3D 717192=3D20
sql=3D3D "select isbn, book_title, publisher_name FROM cs443.books_availa=
bl=3D e where isbn=3D3D " &isbn

The above works but the below doesn't return any thing


Dim pubname
Pubname =3D3D "Bantam Books"

sql=3D3D "select isbn, book_title, publisher_name FROM cs443.books_availa=
bl=3D e where publisher_name =3D3D " &pubname=3D20
********************************************
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.
********************************************

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg