Re: SQL

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/16
Message-ID: <8idape$lnc$1_at_nnrp1.deja.com>#1/1

In article <3949EB1F.4D6048C0_at_hotmail.com>,   kskasi_at_hotmail.com wrote:
> Hello everyone
>
> I've got a table named projects which has got customer_budget in it.
> This customer_budget is for 12 months of the year. Basically I need to
> spread that out throughout the year. So if the customer budget is $12
> the output should look something like this
>
> Period Budget
> 200001 $1
> 200002 $1
> 200003 $1
> .
> .
> .
> 200012 $1
>
> Period is to_char(date,'YYYYMM). Is it possible to get that output
 for
> a given date range
>
> cheers...kasi
>
>

you have to figure out how to do the rounding ;)

ops$tkyte_at_8i> create table t ( custid int, budget number );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> insert into t values ( 1, 12 );

1 row created.

ops$tkyte_at_8i> insert into t values ( 2, 25 );

1 row created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> select custid, budget/12,
  2    to_char( add_months( trunc(sysdate,'year'), r-1 ), 'yyyymm' )

  3 from t, ( select rownum r from all_objects where rownum <= 12 )   4 /

    CUSTID BUDGET/12 TO_CHA
---------- ---------- ------

         1          1 200001
         2 2.08333333 200001
         1          1 200002
         2 2.08333333 200002
         1          1 200003
         2 2.08333333 200003
         1          1 200004
         2 2.08333333 200004
         1          1 200005
         2 2.08333333 200005
         1          1 200006
         2 2.08333333 200006
         1          1 200007
         2 2.08333333 200007
         1          1 200008
         2 2.08333333 200008
         1          1 200009
         2 2.08333333 200009
         1          1 200010
         2 2.08333333 200010
         1          1 200011
         2 2.08333333 200011
         1          1 200012
         2 2.08333333 200012

24 rows selected.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Fri Jun 16 2000 - 00:00:00 CEST