SQL Server Performance Forum – Threads Archive

Qry Help

The qry lists all open Fail trades from x Fails table grouped by CNo and AccNo. The Pair off report displays all open tags that can be paired off against each other for the same security by quantity. This is the case when the client bought and decided to sell out; hence the trades result in perfect matches. CODs are matched against COR or FTR and CORs are matched against COD or FTD. It display the all open tags that can be paried off against each other for the same security by quantity. sample data
AcNo CNo Type Qty
————————————————
70-30000-000649296AC4FTR750.0
60-40000-000G2024KAA4FTD1100.0
70-30000-000482584109FTR800.0
60-40000-00074514M204FTD200.0
70-30000-000482584109FTR800.0
70-30000-000D9541C379FTR69.0
60-40000-000G9212RBE7FTD25000.0
70-30000-000EKE55U103FTR89361.0
89-72002-062X00311104COD3.0
70-30000-000962990107FTR250.0
70-30000-00030382NAC8COR126238.0 i havte written like this
Select a.AccN0 ,
a.CNo ,
NetQty=Sum(a.Qty )+ Sum(b.Qty* -1 )
From x a ,
x b
Where a.AccNo = b.AccNo and
a.CNo = b.CNo and
a.Type in (‘COD’,’FTR’) and
b.Type in (‘COR’,’FTD’)
Group by a.AccNo, a.CNo
Having Sum(a.Qty)+ Sum(b.qty* -1) = 0 Desc: i want select Cno,Accno and calculate net quantity grouped by cno,accno..if netqty is zero..records should display based on above conditions Im not sure this qry is correct…can u pl let me know any ther way
thanks
SURYA

---

Is your query working? Do you have any other Types besides COD, FTR, COR, and FTD because it doesn’t reflect in your sample data? Also, what are you trying to achieve with NetQty=Sum(a.Qty )+ Sum(b.Qty* -1 ), especially what is NetQty .. is it an alias to the summed values? Try this; it is a simplified version of what I think you are trying to achieve:
SELECT AccNO, CNo, (SUM(Qty)+ SUM(Qty*-1)) AS NetQty
FROM X
GROUP BY AccNo, CNo, Qty
HAVING SUM(Qty)+ SUM(Qty* -1) = 0 Let me know if it works. – Tahsin

---

]]>