SQLTeam.com | Weblogs | Forums

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

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

What have you tried

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