locked
How get conflicted part that have same status two time based on partId and LastProcessingdate ? RRS feed

  • Question

  • User696604810 posted

    problem

    How get conflicted part that have same status two time based on partId and LastProcessingdate ?

    I work on sql server 2012 I need to update status on table with message 'conflict status per part'

    where part have same status two time or twice . 

    so if part x have status y two time then conflict 

    so according to sample I made two parts must have conflict status

    3054 and 3090 because every part have more status same

    so How to do that please 

    drop table #duplicateparts
    create table #duplicateparts
    (
    Id int identity (1,1),
    LifeCycleId int,
    PartId  int,
    Zlc  nvarchar(100),
    LastProcessingDate datetime,
    Status nvarchar(200)
    )
    insert into #duplicateparts(LifeCycleId,PartId,Zlc,LastProcessingDate,Status)
    values
    (500122,3054,'Active','01-04-2020',null),
    (500123,3054,'ActivePreview','02-04-2020',null),
    (500124,3054,'Active','03-04-2020',null),
    (500230,5055,'OBS','01-03-2020',null),
    (500231,5055,'ActivePreview','01-05-2020',null),
    (500232,5055,'Active','01-06-2020',null),
    (500350,3090,'Active','10-04-2020',null),
    (500351,3090,'Active','11-04-2020',null),
    (500450,4002,'Active','08-04-2020',null)

    Expected result

    Id	LifeCycleId	PartId	Zlc	LastProcessingDate	Status
    1	500122	3054	Active	2020-01-04 00:00:00.000	conflict status per part
    2	500123	3054	ActivePreview	2020-02-04 00:00:00.000	conflict status per part
    3	500124	3054	Active	2020-03-04 00:00:00.000	conflict status per part
    4	500230	5055	OBS	2020-01-03 00:00:00.000	NULL
    5	500231	5055	ActivePreview	2020-01-05 00:00:00.000	NULL
    6	500232	5055	Active	2020-01-06 00:00:00.000	NULL
    7	500350	3090	Active	2020-10-04 00:00:00.000	conflict status per part
    8	500351	3090	Active	2020-11-04 00:00:00.000	conflict status per part
    9	500450	4002	Active	2020-08-04 00:00:00.000	NULL

    Tuesday, April 21, 2020 10:26 AM

Answers

  • User452040443 posted

    Try:

    with CTE_Count as
    (
        select distinct PartId from #duplicateparts
        group by PartId, Zlc
        having count(1) > 1
    )
    
    update d
    set d.Status = 'conflict status per part'
    from #duplicateparts as d
    where exists(select 1 from CTE_Count as c where c.PartId = d.PartId)
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 21, 2020 8:07 PM