Avatar of SimonPrice33
SimonPrice33
 asked on

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.CCY1LIMITCCYRATE
FROM         
	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
)

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
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

Open in new window

ASKER CERTIFIED SOLUTION
John_Vidmar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SimonPrice33

ASKER
Thank you,

we have been given the answer by the vendor now, but I have tested the code and am happy with that thank you.

the way this is done is pretty messed up and the person who created the database should be shot
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SimonPrice33

ASKER
thank you