Help in query needed

From: <hartono_nugroho_at_attglobal.net>
Date: Sun, 28 Jan 2001 23:09:14 +1100
Message-ID: <3a744667_2@news1.prserv.net>

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 Received on Sun Jan 28 2001 - 06:09:14 CST