How to select code type 1900 and 1885 when exist at least one time per part?

Transact-SQL
, ,
1

How to select code type 1900 and 1885 when exist at least one time per part ?

I work on sql server 2012 I face issue I can't select Parts from table trades
that exist at least one time per for code type 1900 and 1885

so i need to make query select and get parts that

1- have code type 1885 or code type 1900 or both

2- if part id have code type 1885 or 1900 multiple time per part display it as part 20890

3- if code type have 1885 and have also code type 1995 not display part as 22390

4-if part have code type 1900 and code type 3400 not display part as 27981

what i try
select * from #PartsTransactions where codetype in (1885,1900)
but what i do on another case

so how to make query do all all points above and give me result below

create table #PartsTransactions
 (
 TradeCodesId int identity(1,1),
 PartId int,
 CodeType int,
 Code int,
 PartLevel int
 )
 insert into #PartsTransactions(PartId,CodeType,Code,PartLevel)
 values
 (12590,1885,1000981,0),
 (14320,1700,4321094,0),
 (14320,1885,8551094,0),
 (14320,1900,8925678,0),
 (14320,1300,5876541,0),
 (55321,1900,1124338,0),
 (12590,1900,0198222,0),
 (12590,1885,7023339,0),
 (12890,1885,9904455,0),
 (12890,5431,6667789,0),
 (12590,7000,8765877,0),
 (12590,8000,4441322,0),
 (15320,3000,5901134,0),
 (15320,2500,5000111,0),
 (20890,1885,5790000,0),
 (20890,1885,7777722,0),
 (22390,1885,9801111,0),
 (22390,1995,4443211,0),
 (22390,6000,2234343,0),
 (25792,1900,8999011,0),
 (25792,2500,9000001,0),
 (27981,1900,9876411,0),
 (27981,3400,9011118,0),
 (27981,2800,7770002,0)

expected result

image

2

What have you tried

3

It's not really clear what result you want -- it never is -- but I think should give you enough to get what you need even if it's not 100% what you want:

;WITH cte_parts_counts AS (
    SELECT PartId, 
        SUM(CASE WHEN CodeType = 1885 THEN 1 ELSE 0 END) AS Count_1885,
        SUM(CASE WHEN CodeType = 1900 THEN 1 ELSE 0 END) AS Count_1900,
        SUM(CASE WHEN CodeType = 1995 THEN 1 ELSE 0 END) AS Count_1995,
        SUM(CASE WHEN CodeType = 3400 THEN 1 ELSE 0 END) AS Count_3400
    FROM #PartsTransactions
    WHERE CodeType IN (1885, 1900)
    GROUP BY PartId
)
SELECT 
    PT.*,
    CASE WHEN Count_1885 > 1 OR Count_1900 > 1 THEN 20890
         WHEN Count_1885 >= 1 AND Count_1995 = 0 THEN 22390
         WHEN Count_1900 >= 1 AND Count_3400 = 0 THEN 27981
         ELSE 0 /*?*/
    END AS [PartLevel?]
FROM #PartsTransactions PT
INNER JOIN cte_parts_counts cpc ON cpc.PartId = PT.PartId
ORDER BY PT.PartId