Case statement in SP based on COUNT condition

Transact-SQL
1

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?

2

will be hard to help you with partial code, no schema and no data. Please provide table schema and sample data. help us help you

3

tbl.TR sample data

| PCode | IAMID | Quantity |

| ABC123 | D2Z | 45 |
| ABC123 | D2Z | 12 |
| ABC123 | D3Y | 41 |
| ABC123 | E45 | 12 |
| XYZ456 | ZA5 | 25 |
| XYZ456 | ZA5 | 37 |

Expected output:

| PCode | IAMID | Flag |

| 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'

4

please post the DDL and DML in proper SQL format

create table tblTR( PCode  varchar(10), IAMID varchar(10), Quantity int)

insert into tblTR
select 'ABC123',  --etc
5

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...

Declare @testTable Table (PCode varchar(10), IAMID char(3), Quantity int);
 Insert Into @testTable (PCode, IAMID, Quantity)
 Values ('ABC123', 'D2Z', 45)
      , ('ABC123', 'D2Z', 12)
      , ('ABC123', 'D3Y', 41)
      , ('ABC123', 'E45', 12)
      , ('XYZ456', 'ZA5', 25)
      , ('XYZ456', 'ZA5', 37);

 Select tt.PCode
      , tt.IAMID
      , Flag = iif(count(*) > 1, 'M', 'N')
      , TotalQuantity = sum(tt.Quantity)
   From @testTable            tt
  Group by
        tt.PCode
      , tt.IAMID;