Finding duplicate and illogical entries 1

[roedelfroe]

Hello to everybody.

Following problem:

Due to a error I got in a table duplicate and illogical entries.

It's a table with staff members witch normally should show something like this:

Storage_ID valid_from valid_to personnel_number ....

1 2006/07/07 2007/05/31 1234567
2 2007/06/01 9999/12/31 1234567

Of course this table has more columns ;-)

As you may see, every time there is a change for a Staff member (e.g. new departement), the old entry will be closed and a new created.

Due to a problem I've got now follwing situation:

A)
Storage_ID valid_from valid_to personnel_number ....

1 2006/07/07 2007/05/31 1234567
2 2007/06/01 9999/12/31 1234567
3 2007/06/01 9999/12/31 1234567


B)
Storage_ID valid_from valid_to personnel_number ....

1 2006/07/07 2007/05/31 1234567
2 2007/06/01 9999/12/31 1234567
3 2007/06/02 9999/12/31 1234567

I know there is only a slight difference between A) and B) but o.k.

Now my question:

Is it possible to find all personnel_numbers with this problems? If yes, how?

Further, is it possible to delete such entries "in one go"? Or are there any other suitable solutions?

Thanks for your help in advance!

Roland

 

[SantaMufasa]

Roland,

Here are sample data that I have consolidated from your examples, above:

select * from staff;

STORAGE_ID VALID_FRO VALID_TO  PERSONNEL_NUMBER
---------- --------- --------- ----------------
         1 07-JUL-06 31-MAY-07          1234567
         2 01-JUN-07 31-DEC-99          1234567
         3 01-JUN-07 31-DEC-99          1234567
         4 02-JUN-07 31-DEC-99          1234567

4 rows selected.

Here is code that identifies the rows that are erroneous (and which code should, later, delete):

select distinct y.*
  from staff x, staff y
 where x.personnel_number = y.personnel_number
   and y.storage_id > x.storage_id
   and (y.valid_from between x.valid_from and x.valid_to
           or y.valid_to between x.valid_from and x.valid_to);

STORAGE_ID VALID_FRO VALID_TO  PERSONNEL_NUMBER
---------- --------- --------- ----------------
         3 01-JUN-07 31-DEC-99          1234567
         4 02-JUN-07 31-DEC-99          1234567

Here is code that deletes erroneous rows:

delete from staff z
 where exists (select null from staff x, staff y
                where x.personnel_number = y.personnel_number
                  and y.storage_id > x.storage_id
                  and (y.valid_from between x.valid_from and x.valid_to
                          or y.valid_to between x.valid_from and x.valid_to)
                  and z.rowid = y.rowid);

2 rows deleted.

...and proof that the bad rows are gone:

select * from staff;

TORAGE_ID VALID_FRO VALID_TO  PERSONNEL_NUMBER
--------- --------- --------- ----------------
        1 07-JUL-06 31-MAY-07          1234567
        2 01-JUN-07 31-DEC-99          1234567

 rows selected.
****************************************************************************

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[roedelfroe]

Thank you! That's it.

Roland