Expert Help Required to crack problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Expert Help Required to crack problem

Author

Topic

Sn1per79
Starting Member

2 Posts

Posted - 2005-02-25 : 07:43:50

Im having trouble trying to get the right information from our databases.

We have a number of tables which contain the column UPN.

Im trying to create a query which removes the duplicates where necessary....

There are duplicate people in the database but with different person_id's and one record has the UPN against it.

Scenarios (THESE ARE SIMILAR!)....

SCENARIO 1.
---------------------------
Person_table
Person_id Surname Forename Dob Role Latest_Change_Date
1 Jones John 01/01/1980 1 (student) 01/01/2005 12:00
2 Jones John 01/01/1980 128 (applicant) 01/01/2005 12:00

Stud_student_table
Person_id Upn
1 NULL

Applicant_table
Person_id upn
2 111111

REQUIRED SOLUTION
I need to find the matching person using Surname, Forename and DOB if there is one, and if it's an applicant I need to remove
the UPN from the Applicant_table. This is the same person just duplicated in the database. In this case I don't care when the record
was last changed.

SCENARIO 2.
---------------------------
Person_table
Person_id Surname Forename Dob Role Latest_Change_Date
3 Smith Joe 01/01/1981 1 (student) 01/01/2005 12:00
4 Smith Joe 01/01/1981 1 (student) 12/12/2001 12:00

Stud_student_table
Person_id Upn
3 NULL
4 222222 -- NOTE: EARLIEST RECORD HAS THE UPN, NEEDS TO BE REMOVED.

REQUIRED SOLUTION
Again, I need to find the matching person using Surname, Forename and DOB if there is one, then I need to remove the the earliest
changed record (in this case it is person_id = 4)

SCENARIO 3.
---------------------------
Person_table
Person_id Surname Forename Dob Role Latest_Change_Date
5 Bloggs Fred 01/01/1981 1 (student) 01/01/2005 12:00
6 Bloggs Fred 01/01/1981 129 (student AND Applicant) 12/12/2001 12:00

Stud_student_table
Person_id Upn
5 NULL
6 222222

Aplicant_Table
Person_id Upn
6 222222 -- NOTE: EARLIEST RECORD HAS THE UPN, NEEDS TO BE REMOVED.

REQUIRED SOLUTION

Again, I need to find the matching person using Surname, Forename and DOB if there is one, then I need to remove the the earliest
changed record (in this case it is person_id = 6) from both applicant and person.

Thanks in advance.

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-25 : 14:41:14

I think you need replication to solve this for you using linked servers. If each set of data is placed on it's own partition with proper constraints it should be a simple sql statement

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-26 : 19:59:17

SCENARIO 1.
---------------------------
delete from Applicant_table where Person_id IN
(
select p.Person_id from Person_table p where p.Role='applicant' and exists
(select * from Person_table pp where pp.Surname=p.Surname and pp.Forename=p.Forename
and pp.DOB=p.DOB and pp.Role='student')
)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-26 : 20:21:01

SCENARIO 2.
---------------------------
delete from Stud_student_table where Person_id IN
(
select p.Person_id from Person_table p where p.Role='student' and exists
(select * from Person_table pp where pp.Surname=p.Surname and
pp.Forename=p.Forename and pp.DOB=p.DOB and pp.Role='student' and
pp.Latest_Change_Date > p.Latest_Change_Date)
)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-26 : 20:55:35

> SCENARIO 3.
> ---------------------------
> then I need to remove the the earliest changed record
> (in this case it is person_id = 6) from both applicant and person.

From Person_table OR from Stud_student_table?

Also, why do you stress on (in bold):

Person_id Upn
6 222222 -- NOTE: EARLIEST RECORD HAS THE UPN, NEEDS TO BE REMOVED.

Should it be checked before removing that UPN is not null?
I assumed it's always not null if refers to an (earlier) duplicate record.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources