On Wednesday May 16 2007 4:23 am, Frank Schönheit - Sun Microsystems
Germany wrote:
> Hi Dan,
>
> > Maybe I do not understand SQL well enough, but I have a
> > problem. I have a table for fuel that I buy as well as the
> > odometer reading when it is bought. I would like to calculate the
> > fuel economy (quantity of fuel divided by the distance traveled).
> > How do I subtract the odometer readings from adjacent rows to get
> > the distance traveled? I have a few other databases which require
> > subtracting one row of a field from other row.
>
> speaking strictly, relational database tables don't have a concept
> like "subsequent rows". That is, even if you insert rows in a
> specific order, you're not guaranteed they're returned in the same
> order - the database engine is free to change it. (In fact, some
> known engines do.)
>
> Okay, that was the nitpicking part, since you of course have an
> additional field (the date when you bought the fuel) which defines
> an "order" in the data records.
>
> However, standard SQL does not provide a possibility to refer to
> previous rows - that is, you cannot build an SQL query which
> subtracts the current row value from the previous row value (or the
> other way round).
>
> Such tasks are best solved in the reporting domain. Now, well, the
> reporting facilities which come with OOo 2.2 are not able to do
> this. Sad enough. So, if you're bound to 2.2, the only suggestion I
> (don't like to, but) can give: create a data range in a
> spreadsheet, which is filled from your table, and do the
> calculations in the spreadsheet.
>
> Besides this, I want to point you to the new report designer which
> will be available in 2.3. Therein, you will be able to solve this
> problem.
>
> If you're a bleeding-edge type, I suggest you wait for the
> announcement [1] of a public alpha (this week, hopefully), and try
> it out - every feedback in this early development phase is be
> appreciated, and will help us to improve the designer before the
> first public release.
>
> Ciao
> Frank
<snip>
I think I have now found a way, but it involves an extra "step"
(actually an extra query). Create a table with the following fields:
FuelID (PK), Date, FuelCost, FuelQuanity, and Odometer. Then create a
query (Query1) with these fields from the table: FuelID, FuelQuanity,
and Odometer. Create a second query (Query2). Its fields are:
Fuel.Date, Fuel.FuelCost, Query1.FuelQuanity,
(Query1.Odometer-Fuel.Odometer)/Query1.FuelQuanity, and
Query1.FuelID-Fuel.FuelID. Set two Criterion: Query1.FuelID -
Fuel.FuelID = 1; and
(Query1.Odometer-Fuel.Odometer)/Query1.FuelQuanity >0.
I can now use information from two consecutive rows to make
calculations. By the way, my background is a BS degree in mathematics
which included courses in topology. (Relational databases seem to
work similar to a topology.) I have a MS degree in Education with
emphasis in mathematics. I was hoping to be able to do it one one
query, but no such luck. With my math background, I was wondering if
it could then be done in several steps. It appears this is the case.
Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
