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 Send Private Message
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 Send Private Message
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)