SQLTeam.com | Weblogs | Forums

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

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

;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
1 Like

Exactly it is correct
thanks