I need to put a case statement in a stored procedure to flag one of the fields as either 'M' or 'N'. The condition in the source table (tbl.TR) is the combination of PCode and IAMID. The case statement therefore must determine if the record in the source table (TR) is the only 1 record in tbl.TR with the combination of that specific PCode and IAMID (then flag as 'N') or if there are more than 1 record in the source table (TR) with that combination, then flag as 'M'.
My JOIN looks as follows:
INNER JOIN [dbo].[Trades] AS TR
ON TR.PCode = P.ACCT_ID
AND TR.IAMID = P.ISS_ID
The TR table contains the following:
PCode, IAMID, Quantity
If there is only 1 unique record in TR with a specific PCode and specific IAMID then flag as 'N' else flag as 'M' if there are more than 1 record in TR with the same PCode and IAMID combination.
I tried the following case statement but it's not returning the correct results:
WHEN COUNT(TR.Code + TR.IAMID) > 1 then 'M'
WHEN COUNT(TR.Code + TR.IAMID) = 1 then 'N'
ELSE ' '
Is the join perhaps incorrect or the Case statement?