ahmed elbarbary

ahmed elbarbary

  • NA
  • 1.6k
  • 256k

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

Mar 18 2021 8:46 PM

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
 
  1. create table #PartsTransactions  
  2.  (  
  3.  TradeCodesId int identity(1,1),  
  4.  PartId int,  
  5.  CodeType int,  
  6.  Code int,  
  7.  PartLevel int  
  8.  )  
  9.  insert into #PartsTransactions(PartId,CodeType,Code,PartLevel)  
  10.  values  
  11.  (12590,1885,1000981,0),  
  12.  (14320,1700,4321094,0),  
  13.  (14320,1885,8551094,0),  
  14.  (14320,1900,8925678,0),  
  15.  (14320,1300,5876541,0),  
  16.  (55321,1900,1124338,0),  
  17.  (12590,1900,0198222,0),  
  18.  (12590,1885,7023339,0),  
  19.  (12890,1885,9904455,0),  
  20.  (12890,5431,6667789,0),  
  21.  (12590,7000,8765877,0),  
  22.  (12590,8000,4441322,0),  
  23.  (15320,3000,5901134,0),  
  24.  (15320,2500,5000111,0),  
  25.  (20890,1885,5790000,0),  
  26.  (20890,1885,7777722,0),  
  27.  (22390,1885,9801111,0),  
  28.  (22390,1995,4443211,0),  
  29.  (22390,6000,2234343,0),  
  30.  (25792,1900,8999011,0),  
  31.  (25792,2500,9000001,0),  
  32.  (27981,1900,9876411,0),  
  33.  (27981,3400,9011118,0),  
  34.  (27981,2800,7770002,0)  
expected result as below 
79317-image.png 

Answers (1)