question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked EchoLiu-msft edited

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

 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


79317-image.png



sql-server-generalsql-server-transact-sql
image.png (16.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Try:

 select * from #PartsTransactions
 where PartId in ((select distinct PartId from #PartsTransactions where CodeType=1885 
                   except
    select distinct PartId from #PartsTransactions where CodeType=1995
    union all
    select distinct PartId from #PartsTransactions where CodeType=1900
    except
    select distinct PartId from #PartsTransactions where CodeType=3400))

Regards
Echo


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

I think the output should include

79404-image.png


Try this:

 ;WITH CTE_PartCount AS (
     SELECT PartId, COUNT(*) AS PartCount
     FROM #PartsTransactions
     GROUP BY PartId
 ),
 CTE_Part_22390 AS (
     SELECT 22390 AS PartId, COUNT(DISTINCT CodeType) AS CountNumber
     FROM #PartsTransactions
     WHERE PartId = 22390 AND CodeType IN (1885, 1995)
 ),
 CTE_Part_27981 AS (
     SELECT 27981 AS PartId, COUNT(DISTINCT CodeType) AS CountNumber 
     FROM #PartsTransactions
     WHERE PartId = 27981 AND CodeType IN (1900, 3400)
 )
    
 SELECT * 
 FROM #PartsTransactions
 WHERE PartId NOT IN (
     SELECT PartId FROM CTE_PartCount WHERE PartCount > 2
 )
 OR PartId IN (
     SELECT PartId FROM CTE_PartCount WHERE PartCount > 2
     AND PartId NOT IN (SELECT PartId FROM CTE_Part_22390 WHERE CountNumber > 1)
     AND PartId NOT IN (SELECT PartId FROM CTE_Part_27981 WHERE CountNumber > 1)
 );

image.png (2.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.