It could be that CLIENT_ID is never null at all, perhaps it contains spaces or its length is zero, I will suggest to filter the data as follow:
WHERE [CLIENT_ID] IS NOT NULL AND LEN( TRIM ( [CLIENT_ID] ) ) > 0
The expression LEN( TRIM ( [CLIENT_ID] ) ) returns a positive value when [CLIENT_ID] is not null and contains some information, the TRIM() function removed leading and trailing spaces, and LEN() returns the length of whatever is left.
Hope this helps,
Thanks for the prompt reply, Arnaldo!
My apologies, I should have been more specific.
It's not that it's not working insofar that it's still pulling through blanks records - it's actually failing to execute at all, wherever and however I try to enter the script.
I get the red "error" message - either "incorrect syntax near WHERE" or "incorrect syntax near ','" or near "FROM" or near anything at all.
If that is the case, then you have a syntax error in your select statement, difficult to find as the previous developer did not indent the query, I applied some indentation, and you will notice where the code is missing a comma.
WHEN YEAR(CONVERT(DATE,TRANS_DATE,103))=2016 THEN CONVERT(DATE,TRANS_DATE,103)
WHEN YEAR(CONVERT(DATE,TRANS_DATE,103))=2015 THEN DATEADD (dd , 364 , CONVERT(DATE,TRANS_DATE,103) )
WHEN YEAR(CONVERT(DATE,TRANS_DATE,103))=2014 THEN DATEADD (dd , 728 , CONVERT(DATE,TRANS_DATE,103) )
END AS REFERENCE_DATE
, CONVERT(DATE,TRANS_DATE,103) AS [TRANS_DATE]
WHEN PROD_GROUP LIKE '%Widget%' THEN 'Blue Widget'
WHEN PROD_GROUP = 'Flapjacks' THEN 'Pancakes'
END AS PROD_CATEGORY
CASE /* you are missing a comma on this case statement */
WHEN [LEGAL_ENTITY] LIKE '%AA%' THEN 'AB'
END AS Country
WHEN PROD_LN_NAME = 'Squids' THEN 'Squids'
WHEN CATEGORY = 'Playing' OR CATEGORY = 'Sleeping' THEN ‘Breathing'
END AS REFERRAL_TYPE
You are missing a comma at your third CASE or it went missing when you copy and pasted you query.