Home » Other » Training & Certification » Query NOT as per Client logic..plz help (merged 3 cross-posts by bb)
Query NOT as per Client logic..plz help (merged 3 cross-posts by bb) [message #336477] Sun, 27 July 2008 13:05 Go to next message
rhnshk
Messages: 20
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.
Re: Query NOT as per Client logic..plz help (merged 3 cross-posts by bb) [message #336483 is a reply to message #336477] Sun, 27 July 2008 14:03 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above
Previous Topic: subsitute a symbol for a set of data
Next Topic: snapshot too old error
Goto Forum:
  


Current Time: Sun Dec 04 02:46:53 CST 2016

Total time taken to generate the page: 0.03826 seconds