Re: Urgent! Programming Question ,(value of current date/value of prior date)

"Coca" <bigmelon_at_btamail.net.cn> wrote in news:bsf2h3$ci628$2_at_ID-189205.news.uni-berlin.de:

> Hi all,
>
> I am working on a project using Oracle9i
> and encounted a question as belows:
>
> A Table like this:
>
> create table account (sdate date,svalue numeric(16,2))
>
> ======
> some records like:
> sdate svalue
>
> 2003-1-1 2000
> 2003-2-1 3000
> 2003-3-1 3200
> 2003-4-1 1400
> 2003-5-1 2500
> 2003-6-1 2400
>
>
> The records of the table follow this rule: only one record for one
> month, that is, each month has only one record.
>
> The end-user could select any period of date as he will, such as from
> 2003-1-1 to 2003-4-1,
> and my progrom should calculate a financial indicator called indicator1
> whose formula is:
> (value of current date/value of prior date), that is
>
> sdate svalue indicator1
>
> 2003-1-1 2000 null(since the value of 2002-12-1 does not exist
> in the table)
> 2003-2-1 3000 3000/2000=1.5
> 2003-3-1 3200 3200/3000=1.067
> 2003-4-1 1400 1400/3200=0.4375
> ...
>
> How can I do this using one sql sentence,not a stored procedure.
>
> Any tips are appreciated.
>
> Thank you.
>
> Coca

Assuming that all dates are the first of the month

Select to_char(a1.sdate,'YYYY-MM-DD') a1.svalue/a2.svalue indicator1   from account a1, account a2
  where a1.sdate between &begin_date and &end_date   and trunc(a2.sdate)(+) = trunc(add_months(trunca1.sdate,-1));

If you know that all dates have a 00:00:00 time you can eliminate the trunc functions. If dates can be any day of the month, then you need

and to_date(to_char(a2.sdate,'YYYY-MM'||'-01),'YYYY-MM-DD')(+) =

    add_months(to_date(to_char(a1.sdate,'YYYY-MM'||'-01'),'YYYY-MM-DD'),-1)

-- 
Ken Denny
http://www.kendenny.com/