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

Transact-SQL
1

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

2 
;with cte as (select PartID, ZLC, count(1) n
			    from #duplicateparts
			group by PartID, ZLC
			having count(1) > 1)

update d set Status = 'conflict status per part' 
  from #duplicateparts d
	 join cte c
		on d.partID = c.PartID

select * from #duplicateparts
2 Likes
3

Exactly it is correct
thanks