Trade Confirmation Query

---------------------bse-------------------------
XLODBC
1
DSN=sns1011odbc;UID=;Pwd=;ConnectString=sns1011srv;
SELECT sauda.exchcode, sauda.vallan, sauda.brcode, sauda.oowncode, a.fibsacct "Name", aa.cstdno, aa.tel1, aa.tel2, aa.mobile,aaa.email,sauda.compcode "Scrip Code", company.fibsacct "Scrip Name", SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0)) "P.QTY", ROUND(SUM(decode(sauda.buysell, 'B', decode(sauda.saudatype, 'N', (sauda.market -sauda.havala_rate) *sauda.quantity, sauda.quantity *sauda.market), 0)) / decode(SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0)), 0, 1, SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0))), 2) "P.avg",SUM(decode(sauda.buysell, 'S', sauda.quantity, 0)) "S.QTY", ROUND(SUM(decode(sauda.buysell, 'S', decode(sauda.saudatype, 'N', (sauda.market -sauda.havala_rate) *sauda.quantity, sauda.quantity *sauda.market), 0)) / SUM(decode(sauda.buysell, 'S', sauda.quantity)), 2) "S.avg" FROM sauda sauda, company company, ldbo.markclose m, ldbo.accounts a, ldbo.accountaddressdetail aa, ldbo.ACCOUNTEMAILDETAIL aaa WHERE sauda.firmnumber = 'SNS-000001' AND sauda.exchcode = 'BSE' AND sauda.dtoftran = TRUNC(sysdate) AND sauda.firmnumber = a.firmnumber AND sauda.oowncode = a.oowncode AND sauda.firmnumber = aa.firmnumber AND sauda.oowncode = aa.oowncode AND sauda.compcode = company.oowncode AND sauda.firmnumber = company.firmnumber AND sauda.firmnumber = m.firmnumber AND company.firmnumber = m.firmnumber AND sauda.compcode = m.code AND sauda.saudatype = 'N' and aa.oowncode=aaa.oowncode and aa.firmnumber=aaa.firmnumber GROUP BY sauda.exchcode, sauda.vallan, sauda.compcode, company.fibsacct, sauda.oowncode, m.market, sauda.vallan, a.fibsacct, aa.cstdno, aa.tel1, aa.tel2, aa.mobile,aaa.email, sauda.brcode, sauda.dtoftran ORDER BY sauda.oowncode


---------------------nse-------------------------

XLODBC
1
DSN=sns1011odbc;UID=;Pwd=;ConnectString=sns1011srv;
SELECT sauda.exchcode, sauda.vallan, sauda.brcode, sauda.oowncode, a.fibsacct "Name", aa.cstdno, aa.tel1, aa.tel2, aa.mobile,aaa.email,sauda.compcode "Scrip Code", company.fibsacct "Scrip Name", SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0)) "P.QTY", ROUND(SUM(decode(sauda.buysell, 'B', decode(sauda.saudatype, 'N', (sauda.market -sauda.havala_rate) *sauda.quantity, sauda.quantity *sauda.market), 0)) / decode(SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0)), 0, 1, SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0))), 2) "P.avg",SUM(decode(sauda.buysell, 'S', sauda.quantity, 0)) "S.QTY", ROUND(SUM(decode(sauda.buysell, 'S', decode(sauda.saudatype, 'N', (sauda.market -sauda.havala_rate) *sauda.quantity, sauda.quantity *sauda.market), 0)) / SUM(decode(sauda.buysell, 'S', sauda.quantity)), 2) "S.avg" FROM sauda sauda, ldbo.ACCOUNTEMAILDETAIL aaa, company company, ldbo.markclose m, ldbo.accounts a, ldbo.accountaddressdetail aa WHERE sauda.firmnumber = 'SNS-000001' AND sauda.exchcode = 'NSE' AND sauda.egroup IN('NN', 'NW') AND sauda.dtoftran = TRUNC(sysdate) AND sauda.firmnumber = a.firmnumber AND sauda.oowncode = a.oowncode AND sauda.firmnumber = aa.firmnumber AND sauda.oowncode = aa.oowncode AND sauda.compcode = company.oowncode AND sauda.firmnumber = company.firmnumber AND sauda.firmnumber = m.firmnumber AND company.firmnumber = m.firmnumber AND sauda.compcode = m.code AND sauda.saudatype = 'N' and aa.firmnumber=aaa.firmnumber and aa.oowncode=aaa.oowncode GROUP BY sauda.exchcode, sauda.vallan, sauda.compcode, company.fibsacct, sauda.oowncode, m.market, sauda.vallan, a.fibsacct, aa.cstdno, aa.tel1, aa.tel2, aa.mobile,aaa.email, sauda.brcode, sauda.dtoftran ORDER BY sauda.oowncode

------------------------nsef------------------------

SELECT fotransactions.exchcode,fotransactions.saudatype,a.brcode,fotransactions.oowncode,a.fibsacct,aa.cstdno,aa.tel1,aa.mobile,aaa.email,fotransactions.compcode "Scrip Code", company.fibsacct "Scrip Name",company.expirydate,fotransactions.strikeprice,fotransactions.optiontype,SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0)) "P.QTY",ROUND(SUM(decode(fotransactions.buysell, 'B', decode(fotransactions.saudatype, 'N',(fotransactions.market -fotransactions.havala_rate) *fotransactions.quantity, fotransactions.quantity *fotransactions.market), 0)) / decode(SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0)), 0, 1, SUM(decode(buysell, 'B', decode(egroup, 'NA', 0, quantity), 0))), 2) "P.avg",SUM(decode(fotransactions.buysell, 'S', fotransactions.quantity, 0)) "S.QTY",ROUND(SUM(decode(fotransactions.buysell, 'S', decode(fotransactions.saudatype, 'N',(fotransactions.market -fotransactions.havala_rate) *fotransactions.quantity, fotransactions.quantity *fotransactions.market), 0)) / SUM(decode(fotransactions.buysell, 'S', fotransactions.quantity)), 2) "S.avg" FROM ldbo.fotransactions fotransactions,company company,ldbo.accounts a,accountaddressdetail aa,ldbo.ACCOUNTEMAILDETAIL aaa WHERE fotransactions.firmnumber = 'SNS-000001' AND fotransactions.saudatype != 'B/' AND fotransactions.exchcode = 'NSEF' AND fotransactions.dtoftran = TRUNC(sysdate) AND fotransactions.firmnumber = a.firmnumber AND fotransactions.oowncode = a.oowncode AND fotransactions.firmnumber = aa.firmnumber AND fotransactions.oowncode = aa.oowncode AND fotransactions.compcode = company.oowncode AND fotransactions.firmnumber = company.firmnumber AND fotransactions.saudatype != 'B/' AND a.firmnumber = 'SNS-000001' and aa.oowncode=aaa.oowncode and aa.firmnumber=aaa.firmnumber GROUP BY fotransactions.exchcode,aa.cstdno,aa.tel1,aa.mobile,a.fibsacct,a.brcode,company.expirydate,fotransactions.compcode,fotransactions.saudatype,fotransactions.optiontype, company.fibsacct,fotransactions.strikeprice,fotransactions.oowncode,fotransactions.dtoftran,aaa.email ORDER BY fotransactions.oowncode

-----------------------nsec---------------------
select fotransactions.EXCHCODE,fotransactions.saudatype,A.BRCODE,fotransactions.OOWNCODE,A.FIBSACCT,Aa.CSTDNO,Aa.tel1,aa.mobile,aaa.email,fotransactions.compcode "Scrip Code",company.fibsacct "Scrip Name",company.EXPIRYDATE,fotransactions.strikeprice,fotransactions.OPTIONTYPE,ROUND(SUM(DECODE(BUYSELL,'B',DECODE(EGROUP,'NA',0,QUANTITY),0))/COMPANY.MARKLOT,2) "P.QTY",round(Sum(Decode(fotransactions.Buysell,'B',Decode(fotransactions.saudatype,'N' ,(fotransactions.MARKET-fotransactions.Havala_rate)*fotransactions.Quantity,fotransactions.Quantity*fotransactions.MARKET),0))/DECODE(SUM(DECODE(BUYSELL,'B',DECODE(EGROUP,'NA',0,QUANTITY),0)),0,1,SUM(DECODE(BUYSELL,'B',DECODE(EGROUP,'NA',0,QUANTITY),0))),4) "P.avg", ROUND(Sum(Decode(fotransactions.Buysell,'S',fotransactions.Quantity,0))/COMPANY.MARKLOT,2) "S.QTY", round(Sum(Decode(fotransactions.Buysell,'S',Decode(fotransactions.saudatype,'N' ,(fotransactions.MARKET-fotransactions.Havala_rate)*fotransactions.Quantity,fotransactions.Quantity*fotransactions.MARKET),0))/Sum(Decode(fotransactions.Buysell,'S',fotransactions.Quantity)),4) "S.avg" FROM ldbo.fotransactions fotransactions,COMPANY,ldbo.accounts a,ldbo.accountaddressdetail aa,ldbo.ACCOUNTEMAILDETAIL aaa WHERE fotransactions.FIRMNUMBER = 'SNS-000001' and fotransactions.saudatype !='B/' and fotransactions.EXCHCODE='NSEC'and fotransactions.dtoftran=TRUNC(sysdate -1) and fotransactions.firmnumber=a.firmnumber and fotransactions.oowncode=a.oowncode and fotransactions.firmnumber=aa.firmnumber and fotransactions.oowncode=aa.oowncode AND fotransactions.COMPCODE = COMPANY.OOWNCODE AND fotransactions.FIRMNUMBER = COMPANY.FIRMNUMBER and fotransactions.saudatype !='B/' and a.FIRMNUMBER = 'SNS-000001' and aa.oowncode=aaa.oowncode and aa.firmnumber=aaa.firmnumber GROUP BY fotransactions.EXCHCODE,Aa.CSTDNO,Aa.tel1,aa.mobile,aaa.email,COMPANY.MARKLOT,A.FIBSACCT,A.BRCODE,company.EXPIRYDATE,fotransactions.compcode,fotransactions.saudatype,fotransactions.OPTIONTYPE,COMPANY.FIBSACCT,fotransactions.strikeprice,fotransactions.OOWNCODE order by fotransactions.OOWNCODE