January 17, 2014 at 6:34 am
Hi
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,
PersonId uniqueidentifier,
PhoneId int,
IsPrimaryNumber bit,
PhoneExtension varchar(10),
Comments varchar(255))
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:
PersonPhoneIdPersonIdPhoneIdIsPrimaryNumberPhoneExtensionComments
14EE56555-91C6-4755-AC8E-0099D297445A277111111this is phone1
34EE56555-91C6-4755-AC8E-0099D297445A277201111this 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 18 yrs exp.
January 17, 2014 at 6:44 am
Where you have a duplicate, how do you decide which one to throw away? And why do you want to use MERGE? MERGE is used for updating, inserting and/or deleting in the same statement. You've just asked for a result set, which will be a SELECT statement.
John
January 17, 2014 at 6:51 am
Hi
I was thinking to use MERGE because you are basically merging 2 records. As I of the mentioned in 3 rules, we can delete any of the record, provided it has been merged in the record which will stay.
So if the IsPrimaryNumber=1 in the record we choose to delete, then the record which stays should have IsPrimaryNumber=1 if it was 0 earlier.
Pls let me know if I need to provide more elaboration.
one addition: I basically want the table to finally have the records which I mentioned as output of SELECT. Sorry about confusion.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
January 17, 2014 at 7:45 am
My first pass:
CREATE TABLE #EmergencyPhoneNumber(PersonPhoneId int Primary Key,
PersonId uniqueidentifier,
PhoneId int,
IsPrimaryNumber bit,
PhoneExtension varchar(10),
Comments varchar(255))
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')
;
WITH numbertelns AS (
SELECT ROW_NUMBER() OVER (PARTITION BY PersonID, PhoneID ORDER BY PersonPhoneId) AS rownum,
PersonId,
PhoneId,
IsPrimaryNumber,
PhoneExtension,
Comments
FROM #EmergencyPhoneNumber
), grouptelns AS (
SELECT PersonId,
PhoneId,
MAX(PhoneExtension) AS PhoneExtension,
MAX(CAST (IsPrimaryNumber AS TINYINT)) AS IsPrimaryNumber
FROM #EmergencyPhoneNumber
GROUP BY PersonId,PhoneId
), keeprow AS (
SELECT p1.rownum,p1.PersonId,p1.PhoneId,CAST(p2.IsPrimaryNumber AS bit) AS IsPrimaryNumber,p2.PhoneExtension,p1.Comments
FROM numbertelns p1
JOIN grouptelns p2
ON p1.PersonId = p2.PersonId
AND p1.PhoneId = p2.PhoneId
WHERE p1.rownum = 1)
MERGE numbertelns n1
USING keeprow n2
ON n1.PersonId = n2.PersonId
AND n1.PhoneId = n2.PhoneId
AND n1.rownum = n2.rownum
WHEN MATCHED THEN
UPDATE SET n1.PhoneExtension = n2.PhoneExtension,
n1.IsPrimaryNumber = n2.IsPrimaryNumber
WHEN NOT MATCHED BY SOURCE THEN DELETE
;
SELECT * FROM #EmergencyPhoneNumber
DROP TABLE #EmergencyPhoneNumber
The extension logic may be undesired. You didn't say what to do if the duplicates both had extensions and they did not match. In that case I take the highest one.
A merge actually does make some sense here, as you need to both update and delete.
January 17, 2014 at 11:11 am
thx a lot..it worked..
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply