Need to merge into a single record per group, and the data is merged in such a way that we have the most complete set of attributes

SELECT a.*
FROM MRSVoid.dbo.Customer_Dataset$ a
CROSS JOIN
(SELECT 
[Customer_LastName]
,[Customer_FirstName]
,[Customer_AddressLine1]

,[Customer_HomePhone]
,[Customer_InternetEmail]
FROM MRSVoid.dbo.Customer_Dataset$ 
GROUP BY [Customer_LastName],
[Customer_FirstName],
[Customer_AddressLine1],
[Customer_InternetEmail],
[Customer_HomePhone]
HAVING count(*) > 1) b
where ((a.Customer_LastName = b.Customer_LastName) OR (a.Customer_LastName is NULL AND b.Customer_LastName is NULL))
AND ((a.Customer_FirstName = b.Customer_FirstName) OR (a.Customer_FirstName is NULL AND b.Customer_FirstName is NULL))
AND ((a.Customer_AddressLine1 = b.Customer_AddressLine1) OR (a.Customer_AddressLine1 is NULL AND b.Customer_AddressLine1 is NULL))
AND ((a.Customer_InternetEmail = b.Customer_InternetEmail) OR (a.Customer_InternetEmail is NULL AND b.Customer_InternetEmail is NULL))
AND ((a.Customer_HomePhone = b.Customer_HomePhone) OR (a.Customer_HomePhone is NULL AND b.Customer_HomePhone is NULL))
order by Customer_AddressLine1

This query gives me duplicate rows from a dataset, now I need to merge into a single record per group, and the data merged in such a way that we have the most complete set of attributes as possible. Example: a. If two duplicate records share an email address, but only one has a full mailing address, the resultant merged record should have both the email and the mailing address. b. If two duplicate records have different values for one of the following, the merged record should use the more recent attribute as identified by the ModifiedOn and/or CreatedOn timestamp values.

Sample data

ID  CreatedOn   ModifiedOn  Customer_LastName   Customer_FirstName  Customer_AddressLine1   Customer_City Customer_State    Customer_Zip    Customer_HomePhone  Customer_InternetEmail
27196   2012-11-14 18:51:07.000 2012-11-17 15:28:45.000 NULL    David   98 Pelmor Dr    Marmora OR  85044   NULL NULL

14983   2012-11-18 14:02:44.000 2012-11-18 14:02:44.000 NULL    David   98 Pelmor Dr    Marmora OR  85044   NULL NULL

#mysql #sql #asp.net #sql-server #t-sql