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

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

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.

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

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

SCHEMAS in SQL Server -MS SQL Server – Zero to Hero Query Master

This is part 3 of “MS SQL Server- Zero to Hero” and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below.

Welcome Back the T-SQL Debugger with SQL Complete – SQL Debugger

Debug SQL stored procedures and develop your SQL database project with dbForge SQL Complete, a new add-in for Visual Studio and SSMS. When you develop large chunks of T-SQL code with the help of the SQL Server Management Studio tool, it is essential to test the “Live” behavior of your code by making sure that each small piece of code works fine and being able to allocate any error message that may cause a failure within that code.

Hire ASP.Net Developers

Looking to outsource your asp dot net development requirement? ASP.Net is a special feature of the DOT Net framework created by Microsoft. At [HourlyDeveloper.io](https://hourlydeveloper.io/ "HourlyDeveloper.io"), we have a team of experienced...

How to Migrate SQL Server in an ASP.NET MVC Application to MySQL and PostgreSQL

In this blog, we are going to learn the procedure to migrate a SQL Server database in an ASP.NET MVC application to MySQL and PostgreSQL. All three, Microsoft SQL Server, MySQL and PostgreSQL, are relational database management systems (RDBMS). MSSQL is developed and maintained by Microsoft, whereas