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

  • 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

  • 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

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

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