Select query WHERE clause - Exclude specific records

Transact-SQL
1

Hi,

I have a simple usp (basically a Select query). In the WHERE clause I need to exclude a specific value ([Instrument_Classification] of 'MXXOIS') that have a [Price_Currency] in ('CNY', 'BRL', 'CLP', 'THB'). All other [Instrument_Classification] of 'MXXOIS' that have [Price_Currency] of currencies other than the 4 noted above must be included. There are however hundreds of records with a [Instrument_Classification] of NULL, which must be included. The query looks as follows:

SELECT
[Instrument_Classification]
, [Report_Status]
, [Price_Currency]
, [Transaction_Reference_Number]
, [Venue_Transaction_ID]
, [Executing_Entity_ID]
, [Date Received]
FROM [Transactions].[dbo].[TradeMessage]
WHERE
[Date Received] between '2022-10-27 00:00:00' and '2022-10-27 23:59:59'
AND
( [Instrument_Classification] <> 'MXXOIS' and [Price_Currency] in ('CNY', 'BRL', 'CLP', 'THB') or [Instrument_Classification] is NULL )

The query returns all [Instrument_Classification] records with NULL values correctly but excludes all records with [Instrument_Classification} of MXXOIS plus also excludes all other records with [Instrument_Classification} other than 'MXXOIS'

Anyone able to help on my WHERE clause please?

2

hi

please try this and let me know

SELECT
	  [Instrument_Classification]
	, [Report_Status]
	, [Price_Currency]
	, [Transaction_Reference_Number]
	, [Venue_Transaction_ID]
	, [Executing_Entity_ID]
	, [Date Received]
FROM 
	[Transactions].[dbo].[TradeMessage]
WHERE
	[Date Received] between '2022-10-27 00:00:00' and '2022-10-27 23:59:59'
		AND
	( 	
	   ( 
	       [Instrument_Classification] <> 'MXXOIS' 
	           AND 
		   [Price_Currency] in ('CNY', 'BRL', 'CLP', 'THB') 
	   )	   
	   OR 
	   (
	      [Instrument_Classification] is NULL 
		)
     )
3 
WHERE [Date Received] >=  '20221027'
	AND [Date Received] < '20221028'
	AND NOT
	(
		Instrument_Classification = 'MXXOIS'
		AND Price_Currency IN ('CNY', 'BRL', 'CLP', 'THB') 
	);
4

@harrishgg1 -
This one excludes any [Instrument_Classification] code other than NULL or MXXOIS. e.g. [Instrument_Classification] of FFICSX is altogether excluded which is not correct.

@ifor -
This one now excludes all records with [Instrument_Classification] as NULL with [Price_Currency] 'CNY', 'BRL', 'CLP' or 'THB'. Should include those.

5 
WHERE [Date Received] >=  '20221027'
	AND [Date Received] < '20221028'
	AND
	(
		Instrument_Classification IS NULL
		OR NOT
		(
			Instrument_Classification = 'MXXOIS'
			AND Price_Currency IN ('CNY', 'BRL', 'CLP', 'THB')
		)
	);
6

Thanks @Ifor, this works perfectly.

7

To everyone using BETWEEN with the 23:59:59 time... stop it. You're missing the last second of every day. Don't use any version of 23:59, period. Always use >= StartDate and < NextStartDate like @Ifor did.

Do that even for supposedly "Date" only columns, as well. It's just helps you practice doing it the best way and also bullet-proofs your code when someone suddenly decides to change the datatype of a column and start adding times (like what has happened to me a few times).

2 Likes