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) Sun, 27 July 2008 13:05
 rhnshk Messages: 20Registered: 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
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...

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
 BlackSwan Messages: 25578Registered: January 2009 Location: SoCal Senior Member
http://www.orafaq.com/forum/t/88153/0/
 Previous Topic: few OCP related questions (1) [related to Backup/Recovery] (Merged) Next Topic: snapshot too old error
Goto Forum:

Current Time: Tue Aug 22 04:28:09 CDT 2017

Total time taken to generate the page: 0.01204 seconds