Prev
Next
Forums Integration / Customization S3 Customization/Development

Manipulating Duplicate Records

 1 Replies
 0 Subscribed to this topic
 17 Subscribed to this forum
Sort:
Author
Messages
maalimsimo
Veteran Member
Posts: 49
Veteran Member
7/15/2011 2:08 PM
    I have a need to identify duplicate Employee records in a temp file, based on USERID. Then I need to look at the duplicate record sets and look at the Emp Status:
    1. If both (or all) records are INACTIVE, I do not need to report on them (i.e ignore them).

    2, If a set (of duplicate records) has just one ACTIVE record, ignore the whole set.

    3. If both (or all) records are ACTIVE, then report on them.

    Example set:

    {userid}              {Employee}                  {Status}                      {Action}

    {ABCD}                {1234}                         {INACTIVE}
    {ABCD}                {4567)                         {INACTIVE}                  {Ignore the pair}

    {EFGH}                 {9876}                         {ACTIVE}
    {EFGH}                 {5432}                         {INACTIVE}                 {Ignore the pair}

    {IJKL}                   {8315}                          {ACTIVE}
    {IJKL)                   (5138}                          {ACTIVE}                     {Report on them}

    I will appreciate any suggestions as to how to code this in SQL (Oracle).

    Thanks.
    Maalim

       
    Derek Czarny
    Veteran Member
    Posts: 63
    Veteran Member
    7/15/2011 8:24 PM

      Maybe try something like

      select * from temptable t

      where userid in (

      select userid from temptable

       

       

      where status = 'ACTIVE'

      Group by userid

      having count(*)>1)