Обсуждение: SELECT Question

Hi,
i want to calculate the price difference, change% of 2 price records. Is
there an easy way to do that within one query, rather than writing a
function?

Example:

company, price_date, price, change, change_perc
compA, 20060203,100,,,
compA, 20060202,100,,,
compA, 20060201,100,,,

for one, i would like to calculate for one date the changes (price, and
percentage) to the previous day and update the record.

i also want to be able to run that through the entire history to
re-calculate the changes i.e. not only update one record but update all
of these.

Currently I am doing it with a function but I wonder if there is an
easier and faster way.

Any suggestion or help would be appreciated.

Thanks in advance
Alex

On Sat, Mar 04, 2006 at 03:35:02 +1100,
  Alex <alex@meerkatsoft.com> wrote:
> Hi,
> i want to calculate the price difference, change% of 2 price records. Is
> there an easy way to do that within one query, rather than writing a
> function?

You can use a self join to do this. It won't be spectaculatly fast though.
The idea is too join each record to the record with the largest date less
than the date of the current record. Note that on record won't have a match.