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
TradeCodesId PartId CodeType Code PartLevel
1 12590 1885 1000981 0
2 14320 1700 4321094 0
3 14320 1885 8551094 0
4 14320 1900 8925678 0
5 14320 1300 5876541 0
6 55321 1900 1124338 0
7 12590 1900 198222 0
8 12590 1885 7023339 0
9 12890 1885 9904455 0
10 12890 5431 6667789 0
11 12590 7000 8765877 0
12 12590 8000 4441322 0
15 20890 1885 5790000 0
16 20890 1885 7777722 0
20 25792 1900 8999011 0
21 25792 2500 9000001 0

