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

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  January 1, 2021 at 12:29 am

  #3826047

  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

• pietlinden

  SSC Guru

  Points: 63487

  More actions

  January 1, 2021 at 1:26 am

  #3826060

  Wait... something's not right. Your test cases are imcomplete.

  Looks like you want to group by PartID, CompanyID... then get a count

  Something like

  SELECT *
  FROM #PartsData pd
  WHERE partID IN (
  SELECT PartID
  FROM #PartsData
  GROUP BY PartID, CompanyID
  HAVING COUNT(*)>1 
  );

  the test data you provided is not very good for testing the solution you're looking for, though. That may be a part of your problem. I got at least two false positives when I tried it

  • This reply was modified 3 years, 10 months ago by  pietlinden. Reason: retested queries

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  January 1, 2021 at 7:00 am

  #3826110

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 1, 2021 at 7:49 am

  #3826113

  I am just going to say that it doesn't seem like you are really trying that hard to figure out your solutions.  Having said that, here is a solution that should meet your requirements.

  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'),
  (1699,3425,'Pending'),
  (1699,3425,'Pending');
  go
  
  select
    pd.PartId
    , pd.CompanyId
    , pd.[Status]
  from
    #PartsData pd
    inner join (
    select
      PartId,
      CompanyId
    from
    (
    select distinct
      PartId
      , CompanyId
      , [Status]
    from
      #PartsData
    ) pd1
    group by
      PartId,
      CompanyId
    having
      count(*) > 1
    ) pd2
      on pd.CompanyId = pd2.CompanyId
     and pd.PartId = pd2.PartId;
  go
  
  drop table #PartsData;
  go
  

• January 2, 2021 at 9:17 pm

  This was removed by the editor as SPAM