SQL & PL/SQL

Hi Folks,

I am calculating the balance amount (csamt), based on spos + / - csamt.


Tried with analytical functions. kind of stuck up in the query.



Data

FAC Table

TFID--src--------Did------Fid---------csamt
10----KP---------854------7754--------85000
11----KP--------8698------7754--------44000
12----KP--------4545------4444--------47000
13----KP--------7858------9646--------80000
13----KP--------4548------7754--------32000
14----KP--------8799------4544--------60000
15----KP---------435------7754--------66000
16----ms--------6446------9646--------44500
17----KP--------4545------6534--------47800
18----KP--------7858------9646--------86000
19----KP--------4548------4444--------32300
19----KP--------8799------4544--------60000


Trading Table

TID---------Src-------TDate--------Bid--------Sid
10----------KP-------10/02/2009----1548-------6975
11----------KP-------02/02/2009----5468-------7895
12----------KP-------20/02/2009----1258-------6985
13----------KP-------22/02/2009----5468-------7895
14----------KP-------18/02/2009----4669-------6893
15----------KP-------20/02/2009----1548-------6975
16----------MS-------15/02/2009----5468-------7895
17----------KP-------16/02/2009----1258-------6985
18----------KP-------17/02/2009----5468-------7895
19----------KP-------20/02/2009----4669-------6893


FAC_POS Table

FPID--custid--Src-----Did------Fid------Spos
90-----6975---KP------854------7754-----1000
91-----5468---KP------8698-----7754-----1000
92-----6893---KP------4545-----4444-----6000
93-----5468---MS------7858-----9646-----9000
94-----7865---KP------4548-----7754-----1000
95-----6975---KP------435------7754-----1000
96-----6975---KP------6446-----8888-----1000
97-----6985---KP------4545-----6534-----5000
98-----6893---KP------7858-----4444-----6000
99-----5468---MS------8799-----3443-----9000


In Fac Table,
for every Tid in trading there is corresponding Fid in Fac table.

In trading we having bid and sid. if sid matches with custid in FAC_POS then it's a sale : we deduct, else bid matches
then buy : we add.

so formula will be

spos (opening balance) + csamt(bid matches) - csamt (sid matches)

Also the bid amount(buy) to be added the tdate should be less than the sid(sell) tdate.


for example for tid = 10

the tfid in fac is 7754 and has 4 occurences.

now i compare the custid with the bid and sid of trading. if sid matches then its' sale else buy.


so 1000 - 85000 (sell) + 44000 (Buy) - 32000 (sell) (+ 66000, won't include since greater than sell date)


= - 72000

Please give me your valuable suggestions / opinions to build a query.

Thanks.