Re: Help in query needed

In article <3a744667_2_at_news1.prserv.net>,   <hartono_nugroho_at_attglobal.net> wrote:
> Hi all,
>
> I am a student learning SQL using Oracle database. I have a difficulty
> in querying the database that has a 'price-history' table.
> Any help is greatly appreciated.
>
> Suppose, I got a table that consist of the price history of an item
 eg.:
>
> Item Price Effective_Date
>
> Candy $2 1-JAN-1998
> Candy $2.5 1-JAN-1999
> Candy $3 1-JAN-2000
> Candy $3.5 1-Jan-2001
>
> Then I have a table that record the sale of Item eg.:
>
> Item_Purchased Number_Of_Item Date_Of_Sale
>
> Candy 5 24-MAY-
 1998
> Candy 10 24-MAY-
 1999
> Candy 6 24-May-
 2000
>
> I would like to query my revenue for the sale of Candy so that I have:
>
> Item Price Number_Of_Item Revenue
> Candy $2 5

 $10
> Candy $2.5 10 $25
> Candy $3 6

 $18
>
> As you can see, the price per item in revenue report depend on when
 the
> candy
> was purchased.
> Any Idea on how do I achieve that using Oracle SQL? Thanks in advance
> for your help.
>
> Regards,
> Ari
>
>

SQL >Select   S.Item                     Item ,
  2           H.Price                    Price ,
  3           S.QTY                      Number_Of_Item ,
  4           S.Qty * H.Price            Revenue
  5  From   ( Select   H.Item                     Item ,
  6                    Max ( H.EDate )            EDate ,
  7                    R.QTY                      QTY
  8           From     Price_History              H ,
  9                    Sale_Record                R
 10           Where    R.SDate                    >= H.EDate
 11           And      H.Item                     = R.Item
 12           Group By H.Item ,
 13                    R.QTY
 14         )                   S ,
 15           Price_History     H
 16  Where    H.EDate           = S.EDate ;

ITEM            PRICE NUMBER_OF_ITEM    REVENUE
---------- ---------- -------------- ----------
candy               2              5         10
candy             2.5             10         25
candy               3              6         18

3 rows selected.

Sent via Deja.com
http://www.deja.com/ Received on Sun Jan 28 2001 - 16:51:46 CST