1
Answer

How get conflicted part that have same status two time based

ahmed elbarbary

ahmed elbarbary
4y
476
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
  1. drop table #duplicateparts  
  2. create table #duplicateparts  
  3. (  
  4. Id int identity (1,1),  
  5. LifeCycleId int,  
  6. PartId  int,  
  7. Zlc  nvarchar(100),  
  8. LastProcessingDate datetime,  
  9. Status nvarchar(200)  
  10. )  
  11. insert into #duplicateparts(LifeCycleId,PartId,Zlc,LastProcessingDate,Status)  
  12. values  
  13. (500122,3054,'Active','01-04-2020',null),  
  14. (500123,3054,'ActivePreview','02-04-2020',null),  
  15. (500124,3054,'Active','03-04-2020',null),  
  16. (500230,5055,'OBS','01-03-2020',null),  
  17. (500231,5055,'ActivePreview','01-05-2020',null),  
  18. (500232,5055,'Active','01-06-2020',null),  
  19. (500350,3090,'Active','10-04-2020',null),  
  20. (500351,3090,'Active','11-04-2020',null),  
  21. (500450,4002,'Active','08-04-2020',null)  
Expected Result
  1. Id  LifeCycleId PartId  Zlc LastProcessingDate  Status  
  2. 1   500122  3054    Active  2020-01-04 00:00:00.000 conflict status per part  
  3. 2   500123  3054    ActivePreview   2020-02-04 00:00:00.000 conflict status per part  
  4. 3   500124  3054    Active  2020-03-04 00:00:00.000 conflict status per part  
  5. 4   500230  5055    OBS 2020-01-03 00:00:00.000 NULL  
  6. 5   500231  5055    ActivePreview   2020-01-05 00:00:00.000 NULL  
  7. 6   500232  5055    Active  2020-01-06 00:00:00.000 NULL  
  8. 7   500350  3090    Active  2020-10-04 00:00:00.000 conflict status per part  
  9. 8   500351  3090    Active  2020-11-04 00:00:00.000 conflict status per part  
  10. 9   500450  4002    Active  2020-08-04 00:00:00.000 NULL  
 
Answers (1)
Next Recommended Forum