SQLTeam.com | Weblogs | Forums

How to get repeated PartId and Company Id and have different Status?

How to get duplicate on Part Id and Company Id both and have different Status ?

I work on SQL server 2012 I have issue I can't get duplicate on company id and part id

and on same time have different status

as partid 1211 and companyid 3030 repeated two times meaning two rows but with different status

pending and Done

so How to write query do that

   create table #PartsData  
(  
PartId int,  
CompanyId int,  
Status nvarchar(50)  
)  
insert into #PartsData(PartId,CompanyId,Status)  
values  
(1211,3030,'Pending'),  
(1211,3030,'Done'),  
(1599,4812,'NotFound'),  
(1599,4812,'Pending'),  
(9731,4050,'Inprogress'),  
(9731,4050,'Done'),  
(7801,4031,'Pending'),  
(7822,9815,'Pending')  

Expected result :  
  
 PartId    CompanyId    Status  
 1211    3030    Pending  
 1211    3030    Done  
 1599    4812    NotFound  
 1599    4812    Pending  
 9731    4050    Inprogress  
 9731    4050    Done

What have you attempted yourself?

what I tried
select PartId,CompanyId from #PartsData
group by PartId,CompanyId
having count(1) >1
but different status i an't do it
so How to do it

thank you for reply

code above will get repeated rows per companyid and partid and this I need

but i need also different status

meaning

I Need below because repeated and different status

PartId    CompanyId    Status
 1211    3030    Pending
 1211    3030    Done

and I don't need below because repeated and same status

PartId    CompanyId    Status
 1599    4812    NotFound
 1599    4812    NotFound
;WITH CTE AS(select PartId,CompanyId from #PartsData
group by PartId,CompanyId
having count(DISTINCT Status)>1)
SELECT a.* FROM #PartsData a
INNER JOIN CTE c ON a.PartId=cPartId AND a.CompanyId=c.CompanyId

thank you solved