Loading

How get conflicted part that have same status two time based

How get conflicted part that have same status two time based

ahmed elbarbary

6y718Databases & DBA

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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar6y ago
Hi,

You can create one function which will return your status and you can call to get status,
Create function  GetDuplicatePartStatus
(@PartId INT,
@Zlc varchar(5)
)
RETURNS varchar(50)
AS
BEGIN
declare @deuplicateParteStatus varchar(50) = null
IF exists(select PartId,Zlc from duplicateparts
            where PartId = @PartId group by PartId,Zlc having count(Zlc)>1)
BEGIN
set @deuplicateParteStatus= 'conflict status per part'END
return @deuplicateParteStatus
END
Call function in your select statement,
select LifeCycleId,PartId,Zlc,LastProcessingDate,dbo.GetDuplicatePartStatus(PartId,Zlc) as
Status from duplicateparts