I have an emergency Phone Numbers table and it has got some duplicate records. Duplicate is defined
as same PersonId and same PhoneId. Unfortunately duplicate phone is incorrctly defined as both IsPrimaryNumber=1 as well as IsPrimaryNumber=0. I have to delete/Update duplicates with following rules:
Rule 1:If IsPrimaryNumber=1, then final output should have this flag set to 1.
Rule 2:If phoneExtesnion is present in any of the records, it should be returned.If it is present in multiple records, we may pick any.
Rule 3:Rule 2 applies to Comments column as well.
Below is table and data:
CREATE TABLE EmergencyPhoneNumber(PersonPhoneId int Primary Key,
INSERT INTO EmergencyPhoneNumber VALUES(1,'4EE56555-91C6-4755-AC8E-0099D297445A',2771,0,'1111','this is phone1')
INSERT INTO EmergencyPhoneNumber VALUES(2,'4EE56555-91C6-4755-AC8E-0099D297445A',2771,1,'','this is phone2')
INSERT INTO EmergencyPhoneNumber VALUES(3,'4EE56555-91C6-4755-AC8E-0099D297445A',2772,0,1111,'this is phone3')
Expeacted output for above records is:
PersonPhoneId PersonId PhoneId IsPrimaryNumber PhoneExtension Comments
1 4EE56555-91C6-4755-AC8E-0099D297445A 2771 1 1111 this is phone1
3 4EE56555-91C6-4755-AC8E-0099D297445A 2772 0 1111 this is phone3
PS:I'll prefer if it can be done with MERGE statement.
thanks for help
Online Trainer For SQL DBA and Developer @RedBushTechnologies
with 16 yrs exp.