sql select - where column has more than two useable valuues
Hi Experts,
I am working with the below query, and i need to return results for is TRADECODE is New or Existing, however if its Existing to ignore the rows with value NEW.
I would be grateful if someone could help me with this as I am stuck... I have looked around online and see the same example with smaller calls, but nothing with anything of this complexity.
and unfortunately this cant be reduced at the moment.
SELECT DISTINCT V.ENTITYID, V.CUSTOMERMNEMONIC, V.PRODUCTID, V.TRADENUMBER, V.TRADECODE, V.TRADEDATE, V.VALUEDATE, V.CCYPAIR, CASE WHEN V.PRODUCTID = 'FXOTC' THEN V.DIRECTION WHEN SUM(V.CCY1AMOUNT) > 0 THEN 'BUY' ELSE 'SELL' END DIRECTION, LEFT(CCYPAIR,3) CCYID, SUM(V.CCY1AMOUNT) CCYAMOUNT, V.TRADERATE, SUM(V.CCY2AMOUNT) CCY2AMOUNT, V.MTMCCYID, CASE WHEN V.PRODUCTID = 'FX' AND LEFT(v.CCYPAIR,3) <> V.MTMCCYID AND SUM(V.CCY2AMOUNT) <> 0 THEN SUM(MTM.MTMAMOUNT) WHEN V.PRODUCTID = 'FX' AND LEFT(v.CCYPAIR,3) = V.MTMCCYID AND SUM(V.CCY2AMOUNT) <> 0 THEN 0 ELSE SUM(V.MTMLIMITCCYAMOUNT) END MTMAMOUNT, V.MTMSPOTRATE, V.MTMCCY1DF, V.MTMCCY2DF, V.FEECCYID, SUM(V.FEECCYAMOUNT) as FEECCYAMOUNT , V.CCY1REVALRATE, V.CCY2REVALRATE, V.OPTIONEXPOSURETYPE, SUM(V.CCY1OTHERAMOUNT) as CCY1OTHERAMOUNT, SUM(V.CCY2OTHERAMOUNT) as CCY2OTHERAMOUNT, V.STRIKEPRICE, V.PREMIUMDATE, V.PREMIUMCCYID, V.PREMIUMAMOUNT, CASE WHEN V.PRODUCTID = 'FX' THEN CASE WHEN V.TRADECODE IN ('SETTLED','ROLL_SET') AND LEFT(V.CCYPAIR,3) = CP.QUOTEDCCYID THEN SUM(MTM.MTMAMOUNT) ELSE 0 END ELSE CASE WHEN V.TRADECODE IN ('SETTLED','ROLL_SET')THEN SUM(V.MTMLIMITCCYAMOUNT) ELSE 0 END END MTMREALIZED, V.EXTERNALCUSTOMERREFID1, V.EXTERNALCUSTOMERREFID2, V.INTEXTIND, CASE WHEN V.PRODUCTID = 'FXOTC' /* and LEFT(V.DIRECTION,1) = 'B' */ THEN 'N' WHEN V.TRADECODE = 'PREMSET' then 'N' WHEN LEFT(V.CCYPAIR,3) <> CP.QUOTEDCCYID THEN 'Y' ELSE 'N' END EXCLUDECROSSIND, CASE WHEN V.PRODUCTID = 'FX' AND LEFT(v.CCYPAIR,3) <> V.MTMCCYID AND SUM(V.CCY2AMOUNT) <> 0 THEN SUM(MTM.MTMAMOUNT) WHEN V.PRODUCTID = 'FX' AND LEFT(v.CCYPAIR,3) = V.MTMCCYID AND SUM(V.CCY2AMOUNT) <> 0 THEN 0 ELSE SUM(V.MTMLIMITCCYAMOUNT) END MTMLIMITCCYAMOUNT, sum(V.CCY1LIMITCCYAMOUNT) as CCY1LIMITCCYAMOUNT, sum(V.CCY1LIMITCCYOTHERAMOUNT) as CCY1LIMITCCYOTHERAMOUNT, sum(V.CCY2LIMITCCYAMOUNT) as CCY2LIMITCCYAMOUNT, sum(V.CCY2LIMITCCYOTHERAMOUNT) as CCY2LIMITCCYOTHERAMOUNT, sum(V.FEELIMITCCYAMOUNT) as FEELIMITCCYAMOUNT, sum(V.PREMLIMITCCYAMOUNT) as PREMLIMITCCYAMOUNT, sum(V.PAYOUTLIMITCCYAMOUNT) as PAYOUTLIMITCCYAMOUNT, sum(V.LOWERPAYOUTLIMITCCYAMOUNT) as LOWERPAYOUTLIMITCCYAMOUNT, V.CCY1LIMITCCYRATEFROM MARGINEODOPENTRADES V INNER JOIN CUSTOMER C ON V.CUSTOMERMNEMONIC = C.CUSTOMERMNEMONIC INNER JOIN CURRENCYPAIR CP ON ((CP.CCYID1 = LEFT(V.CCYPAIR,3) AND CP.CCYID2 = RIGHT(V.CCYPAIR,3)) or (CP.CCYID1 = RIGHT(V.CCYPAIR,3) AND CP.CCYID2 = LEFT(V.CCYPAIR,3))) and CP.ORGUNITID = 1 LEFT OUTER JOIN TEXTMARGINOPENPOS_MTM MTM ON V.TRADENUMBER = MTM.TRADENUMBER AND V.ENTITYID = MTM.ENTITYID AND V.PRODUCTID = MTM.PRODUCTID AND V.MTMCCYID = MTM.MTMCCYID AND V.TRADECODE = MTM.TRADECODE AND V.VALUEDATE = MTM.VALUEDATE AND V.GUID = MTM.GUID where v.TRADENUMBER = '311000375'--and (v.TRADECODE = 'EXISTING' OR (v.TRADECODE = 'NEW'))and v.TRADECODE in ('EXISTING','NEW')/*WHERE; ((LEFT(CCYPAIR,3) <> LD.LIMITCCYID AND RIGHT(CCYPAIR,3) = LD.LIMITCCYID) OR (RIGHT(CCYPAIR,3) <> LD.LIMITCCYID AND LEFT(CCYPAIR,3) <> LD.LIMITCCYID))*/ GROUP BY V.GUID, V.ENTITYID, V.CUSTOMERMNEMONIC, V.PRODUCTID, V.TRADENUMBER, V.TRADEDATE, V.VALUEDATE, V.CCYPAIR, V.MTMCCYID, LEFT(CCYPAIR,3), V.MTMSPOTRATE, V.MTMCCY1DF, V.MTMCCY2DF, V.TRADERATE, V.TRADEDATE, V.TRADECODE, V.FEECCYID, V.CCY1REVALRATE, V.CCY2REVALRATE, V.OPTIONEXPOSURETYPE, V.STRIKEPRICE, V.PREMIUMDATE, V.PREMIUMCCYID, V.PREMIUMAMOUNT, V.DIRECTION, V.EXTERNALCUSTOMERREFID1, V.EXTERNALCUSTOMERREFID2, V.INTEXTIND, CP.QUOTEDCCYID, V.CCY1LIMITCCYRATE, V.MTMLIMITCCYAMOUNT)
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
Last Comment
SimonPrice33
8/22/2022 - Mon
Ganapathi
A bit of confusion here.
You want to pull the data for the given TRADECODE. It may be New or Existing.
But I am unable to understand this "however if its Existing to ignore the rows with value NEW" statement.
What you wanted to say here?
SimonPrice33
ASKER
it is very confusing... don't worry about that..
I have cheated for the meantime and am removing it using vb.net
heres my code
Dim sqlstr = TextBox1.Text Dim dt As New DataTable Dim Existing As Boolean = False Try With sqlcmd .Connection = sqlconn .CommandText = TextBox1.Text .CommandType = CommandType.Text End With sqlconn.ConnectionString = connstr sqlconn.Open() Using da As New SqlDataAdapter(sqlcmd) da.Fill(dt) End Using If dt.Rows.Count > 0 Then For Each dr As DataRow In dt.Rows If dr.Item(4).ToString = "EXISTING" Then Existing = True End If Next End If If Existing = True Then For Each dr As DataRow In dt.Rows If dr.Item(4).ToString = "NEW" Then dr.Delete() End If Next End If DataGridView1.DataSource = dt Catch ex As Exception End Try sqlconn.Close() End Sub
You want to pull the data for the given TRADECODE. It may be New or Existing.
But I am unable to understand this "however if its Existing to ignore the rows with value NEW" statement.
What you wanted to say here?