OraFAQ Forum: Training & Certification » Query NOT as per Client logic..plz help (merged 3 cross-posts by bb)

Home » Other » Training & Certification » Query NOT as per Client logic..plz help (merged 3 cross-posts by bb)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query NOT as per Client logic..plz help (merged 3 cross-posts by bb) [message #336477]

Sun, 27 July 2008 13:05

rhnshk
Messages: 26
Registered: May 2008

Junior Member

this is the Excel representation of the DATA like how the users of my client is maintaining Buy/Sell txns.

DT         COD         PRICE        PURQTY       PURCOST        BALQTY       BALCOST      AVGPRICE       SOLDQTY       SOLDAMT     NETSELAMT      COSTSALE      GAINLOSS         COMM%
---------- --- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
03/01/2008 BUY         2.221     75000.000    166593.158     75000.000    166593.158    2.22124211          .000          .000          .000          .000          .000       666.373
10/01/2008 SEL         2.226          .000          .000     72000.000    159929.432    2.22124211      3000.000      6677.000      6650.292      6663.726       -13.434        26.708
15/03/2008 BUY         2.306     35000.000     80717.835    107000.000    240647.267    2.24903988          .000          .000          .000          .000          .000       322.871
01/04/2008 SEL         2.880          .000          .000    102000.000    229402.067    2.24903988      5000.000     14400.000     14342.400     11245.199      3097.201        57.600
20/04/2008 BUY         2.169     50000.000    108432.000    152000.000    337834.067    2.22259255          .000          .000          .000          .000          .000       433.728

the main thing tat i am unable to arrive as per my client logic is the COSTSALE & AVGPRICE. His formula for these columns are;
BALQTY formula is simple BALQTY = BALQTY + PURQTY - SOLDQTY

COSTSALE = AVGPRICE * SOLDQTY &
the BALCOST is a running total (BALCOST = BALCOST + PURCOST - COSTSALE)
AVGPRICE = BALCOST / BALQTY

Based on this logic i designed Tables and Form for the above requiremnt.
inside the form, for validation trigger i need an SQL to arrive at the AVGPRICE tat will calculate GAINLOSS.
the table stores CODE, PRICE, QTY, GROSSAMOUNT, COMMAMOUNT, NETAMOUNT, depending upon the Txn CODE & m not storing COSTSALE in the table. i have not defined in the tab-stuc. so i tried to put an SQL for this;

select
SUM(DECODE(CODE,'BUY',NETAMOUNT,-NETAMOUNT)) /
SUM(DECODE(CODE,'BUY',QTY,-QTY))  AS AVGPRICE
from TXN
where txndate < TO_date('25/02/2008','dd/mm/yyyy')

AVGPRICE
-------------
2.17612188

this is what m getting right now, but its NOT calculating GAIN/LOSS correctly, because m unable to incorporate COSTSALE in my query as per my client logic.
and
i dont know how to take care of the COSTSALE. kindly help me come out of this...

ty v much in advnce.

Report message to a moderator