question

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

How to get Part Id that have part level 0 and not have map from ?

How to get Part Id that have part level 0 and not have map from ?

I work on SQL server 2012 I face issue I can't get Parts that have map to

and not have map from for part level 0

so Firstly I get parts that have part level 0

then secondly

if part have code type to 1273200 then it must have code type from 974451

if part have code type to 194480 then it must have code type from 7320911

so I need to display

parts that have code type 1273200

for part level 0 and not have map from 974451

OR

parts that have code type 194480

for part level 0 and not have map from 7320911

sample code

   create table #codes
 (
 PartId int,
 CodeTypeId  int,
 Partlevel int
 )
 insert into #codes(PartId,CodeTypeId,Partlevel)
 values
 ---this is correct----
 (1250,974451,0),  ---map from
 (1250,1273200,0), ---map to
 (1250,7320911,0), ---map from
 (1250,194480,0),  --map to
 ------------------
 --where map from 974451 for part id 1900 for partlevel 0
 (1900,1273200,0),---map to
 (1900,7320911,0),---map from
 (1900,194480,0),--map to
 ------------------
 (2200,974451,0),---map from
 (2200,1273200,0),---map to
 --where map from 7320911 for part id 2200 for partlevel 0
 (2200,194480,0),--map to
 -----------------
 (3400,974451,1),  --where map from 974451 for part id 3400 for partlevel 0 so if 1 it is wrong
 (3400,1273200,0), ---map to
 (3400,7320911,0), ---map from
 (3400,194480,0),  --map to
 ------------------
 -----------------
  --where map from 974451 for part id 3900 for partlevel 0 so if 1 then it is not exist 
 (3900,1273200,0), ---map to
 (3900,1997801,0), 
 (3900,7320911,0), ---map from
 (3900,194480,0),  --map to
    
    
 (5020,974451,1), 
 (5020,1997801,1),
 (5020,7320911,1), --where map from 7320911 for part id 5020 for partlevel 0 if 1 then it is not exist
 (5020,194480,0),  --map to
 ------------------
    
 ------------------
    
  ---map from 974451 not exist for part id 7050 but not care because I need only parts have partlevel 0
 (7050,1273200,1), ---map to
 (7050,7320911,1), ---map from
 (7050,194480,1),  --map to
 -----------------
  ---map from 7320911 not exist for part id 8900 for partlevel 0 if part level 1 then not exist 
 (8900,7320911,1), ---map from
 (8900,194480,0),  --map to
 -----------------
    
  ---map from 7320911 not exist for part id 9200 for partlevel 0  
 (9200,194480,0)  --map to
 -----------------



Expected Result

     PartId CodeTypeId Partlevel
 1900 1273200 0
 2200 194480 0
 3400 1273200 0
 3900 1273200 0
 5020 194480 0
 8900 194480 0
 9200 194480 0



82015-image.png


sql-server-generalsql-server-transact-sql
image.png (7.3 KiB)
· 2
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.

what i try is

select partid,codetypeid from #codes
where partlevel=0 and codetypeid=974451 and codetypeid <> 1273200
group by partid,codetypeid

union all

select partid,codetypeid from #codes
where partlevel=0 and codetypeid=194480 and codetypeid <> 7320911
group by partid,codetypeid

but it give me wrong result

0 Votes 0 ·

Hi @ahmedsalah-1628,

Could you please validate and provide any update?

If below query is not working, please provide more sample data and expected output so that we could proceed with enhancement.

Thanks.

Best regards
Melissa

0 Votes 0 ·

1 Answer

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

Hi @ahmedsalah-1628,

Please refer below and check whether it is working:

 ;with cte as (
     select *,ROW_NUMBER() over (order by partid) rn from #codes 
     where PartId=1250)
 ,cte2 as (
     select partid,CodeTypeId,floor((rn- 1)/2)+1 as groupid
     from cte)
  ,cte3 as ( 
      select distinct  a.PartId,b.CodeTypeId,b.groupid from #codes a
      cross apply (select CodeTypeId,groupid from cte2) b)
  ,cte4 as (
      select a.*,b.PartId PartId1,b.Partlevel from cte3 a
      left join ( select * from #codes where partlevel=0) b
      on a.PartId=b.PartId and a.CodeTypeId=b.CodeTypeId )
  ,cte5 as (
      select PartId,groupid from cte4
      where PartId1 is null
      group by PartId,groupid
      having count(*)=1)
  select distinct a.PartId,c.CodeTypeId,c.Partlevel 
  from cte5 a
  inner join cte3 b on a.PartId=b.PartId and a.groupid=b.groupid
  left join cte4 c on a.PartId=c.PartId and a.groupid=c.groupid
  where PartId1 is not null
  order by PartId

Output:

 PartId CodeTypeId Partlevel
 1900 1273200 0
 2200 194480 0
 3400 1273200 0
 3900 1273200 0
 5020 194480 0
 8900 194480 0
 9200 194480 0

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

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.