How to get PartId from table #trades that have wrong Code ON Code Value To when

Question

Post reply

How to get PartId from table #trades that have wrong Code ON Code Value To when

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
March 8, 2021 at 5:24 pm
Go to Answer
#3855037
How to get PartId from table #trades that have wrong Code ON Code Value To when code type from and code value from Exist ?
How to get PartId from table #trades that have wrong Code ON Code Value To when code type from and code value from Exist ?
I work on SQL server 2012 I have issue I can't get Part Id and code type that have different on Code
on table #trades and table map code value to for same code type
depend on table #map ?
so firstly get part Id and code type from and code value from must exist on table #trades
then check same partid and code value to and code type to and get code value different when Code type to
on table map equal code type exist on table #trades
if code value to not same as table trades code then display it
as Example steps
1- I get from table map code type from 9090 and Code Value from 13456

2- then i will go to table #trade code search for code type 9090 and code value 13456
so i found partid and code type from 9090 and code value from 13456 for partid 1390 exist
3- then check mapped code type to and map code value to
if this part have same code type to and different on code value to
then display it so part id 1390 must display
because code type from 9090 and code value from 13456 exist
and code type to 7070 exist but code value to on map table that has value 13000 not equal 19000
```
create table #trades
  (
  TradeCodesId int identity(1,1),
  PartId int,
  CodeTypeId int,
  Code int,
  PartLevel int
  )
  insert into #trades(PartId,CodeTypeId,Code,PartLevel)
  values
  (1348,9090,13456,0),
  (1348,7070,13000,0),
  (1387,9090,13456,0),
  (1387,7070,13000,0),
  (1390,9090,13456,0),
  (1390,7070,19000,0),
  (1800,9095,13570,0),
  (1800,7075,28000,0),
  (1850,9095,13570,0),
  (1850,7075,74000,0)
     
        
   create table #map
   (
   MapId int,
   CodeTypeFrom int,
   CodeTypeTo int,
   CodeValueFrom int,
   CodeValueTo int
   )
   insert into #map(MapId,CodeTypeFrom,CodeTypeTo,CodeValueFrom,CodeValueTo)
   values
   (3030,9090,7070,13456,13000),
   (3035,9095,7075,13570,14000)

Expected result

 TradeCodesId PartId CodeTypeId Code PartLevel
 6 1390 7070 19000 0
 8 1800 7075 28000 0
 10 1850 7075 74000 0
75497-image.png
```
expected result

Steve Collins

SSCrazy

Points: 2157

March 9, 2021 at 9:00 pm

Answer

#3855655

select t2.*
from #map m
     join #trades t1 on m.CodeValueFrom=t1.Code
                       and m.CodeTypeFrom=t1.CodeTypeId
     join #trades t2 on t1.PartId=t2.PartId
                        and m.CodeTypeTo=t2.CodeTypeId
where m.CodeValueTo<>t2.Code;

TradeCodesIdPartIdCodeTypeIdCodePartLevel
613907070190000
818007075280000
1018507075740000

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.