How to update status with conflict data where chemical temp table have same chem

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  January 25, 2020 at 10:38 pm

  #3720407

  How to update status with conflict data where chemical temp table have same chemical Id on temp table #temp ?

  steps to achieve that

  1- get related parts to part exist on temp table #temp that have same masked id from temp table #parts

  in this case result will be

  PartId MaskId ChemicalId

  200 1000 901

  500 1700 909

  600 1700 909

  2- check on temp table #chemical for partid or related part id for same maskId

  if chemicalid on step 1 different to chemicalid on temp table chemical

  then nothing happen on status .

  if checmicalid on step 1 same chemicalid on temp table chemical

  then status will updated to conflict based on part id .

  Here 200 have 901 chemical id on temp table #temp and on chemical temp table have 901 for 100

  then status will be conflict because it related to same mask id and have same chemical id 901 .

  Here 700 have 909 chemical id on temp table #temp and on chemical temp table have 909 for 500 and 600

  then status will be nothing changed because it related to same mask id and have different chemical id 920.

  Details
  create table #temp
  (
  partid     int,
  maskid     int,
  chemicalid int,
  status  nvarchar(50)
  )
  insert into #temp
  values
  (100,1000,901,null),
  (700,1700,909,null)
  
  create table  #parts
  (
  PartId  int,
  maskId  int
  )
     
  insert into #parts (PartId,maskId)
  values
  (100,1000),
  (200,1000),
  (500,1700),
  (600,1700),
  (700,1700)
  
  
  create table  #Chemical
  (
  Chemicalmasterid  int,
  ChemicalId  int,
  PartId   int
  )                        
  insert into  #Chemical(Chemicalmasterid,ChemicalId,PartId)
  values
  (1 ,901,100),
  (7 ,920,700)
  
  final result
  100     1000 901 conflict chemical id
  700     1700 909     null
  
  what i have tried
  
  update t set status ='conflict chemical id' from #temp t

• Steve Collins

  SSCrazy

  Points: 2157

  More actions

  January 26, 2020 at 2:39 pm

  #3720449

  
  with
  other_parts_cte as (
      select
        p.PartId, t.maskId, t.chemicalid
      from
        #temp t
       join
        #parts p on t.maskId=p.maskId
                    and t.PartId<>p.PartId),
  sum_cte as (
      select
        c.PartId, opc.maskid, opc.ChemicalId,
        sum(case when c.chemicalid=opc.chemicalid then 1 else 0 end) chem_cnt
      from
        #chemical c
       join
        #parts p on c.PartId=p.PartId
       join
        other_parts_cte opc on p.maskId=opc.maskId
      group by
        c.PartId, opc.maskid, opc.ChemicalId)
  select
    sc.PartId, sc.maskid, sc.ChemicalId, 
    case when sc.chem_cnt>0 then 'conflict chemical id' else null end conflict
  from
    sum_cte sc;

   

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