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:
CASE
WHEN COUNT(TR.Code + TR.IAMID) > 1 then 'M'
WHEN COUNT(TR.Code + TR.IAMID) = 1 then 'N'
ELSE ' '
END
Is the join perhaps incorrect or the Case statement?
| ABC123 | D2Z | M |
| ABC123 | D3Y | N |
| ABC123 | E45 | N |
| XYZ456 | ZA5 | M |
Becasue tblTR contains multiple records for the ABC123 & D2Z combination it is flagged as 'M'
The combination for ABC123 & D3Y is only 1 recoird, should then be flagged as 'N'
Not sure how this should be incorporated into your actual query - since you have not provided the test data and results. I am guessing that you want this in addition to other items...