Re: Help in query needed

Thanks alot,

Any idea on how it will perform in the real world application?

Regards,
Ari

<derf23456_at_my-deja.com> wrote in message news:9527q0$ftj$1_at_nnrp1.deja.com...
> 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 Tue Feb 06 2001 - 02:46:45 CST