On Wednesday May 16 2007 10:22 pm, andrew wrote:
> Dan Lewis wrote:
> > 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.
>
> Actually, that is not a general answer.
>
> You are assuming that the FuelID field has some sequence and there
> is no such guarantee.
> Even using an Autoincrement datatype.
>
> Say you forget to enter the information for a given day,
> Then enter the information for days following, later catch your
> mistake and enter the old days values.
> The difference between FuelID values will be something greater then
> 1.
>
> That said, it is not an uncommon technique
> Just recognize that you are building in dependencies external from
> the schema.
> Fail to enter the data in the correct sequence and the results of
> your query will be erroneous.
>
>
> Drew
Actually, it is a general answer. It is a technique of how to
work with sequential records based upon a particular field which
contains the sequence. Granted, anyone entering information in the
wrong sequence will also get erroneous results. But, isn't that where
the expression garbage in garbage out applies? And in my case, the
second query contains two fields: the odometer fields from the table
and the first query next to each other so that any such errors would
be obvious. (I added them since my last email just to view them, but
as you point out they also serve a very important verification
function.)
Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
