Riley Lambert

Riley Lambert

1550559713

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

What is GEEK

Buddha Community

Lyly Sara

1550560662

You can use row_number() window function

with cte as
(
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))
)

select * from 
(
select *, row_number() over(partition by Customer_LastName,Customer_FirstName,  Customer_AddressLine1 order by ModifiedOn desc) as rn from cte
)A where rn=1

Siphiwe  Nair

Siphiwe Nair

1620466520

Your Data Architecture: Simple Best Practices for Your Data Strategy

If you accumulate data on which you base your decision-making as an organization, you should probably think about your data architecture and possible best practices.

If you accumulate data on which you base your decision-making as an organization, you most probably need to think about your data architecture and consider possible best practices. Gaining a competitive edge, remaining customer-centric to the greatest extent possible, and streamlining processes to get on-the-button outcomes can all be traced back to an organization’s capacity to build a future-ready data architecture.

In what follows, we offer a short overview of the overarching capabilities of data architecture. These include user-centricity, elasticity, robustness, and the capacity to ensure the seamless flow of data at all times. Added to these are automation enablement, plus security and data governance considerations. These points from our checklist for what we perceive to be an anticipatory analytics ecosystem.

#big data #data science #big data analytics #data analysis #data architecture #data transformation #data platform #data strategy #cloud data platform #data acquisition

Uriah  Dietrich

Uriah Dietrich

1618457700

What Is ETLT? Merging the Best of ETL and ELT Into a Single ETLT Data Integration Strategy

Data integration solutions typically advocate that one approach – either ETL or ELT – is better than the other. In reality, both ETL (extract, transform, load) and ELT (extract, load, transform) serve indispensable roles in the data integration space:

  • ETL is valuable when it comes to data quality, data security, and data compliance. It can also save money on data warehousing costs. However, ETL is slow when ingesting unstructured data, and it can lack flexibility.
  • ELT is fast when ingesting large amounts of raw, unstructured data. It also brings flexibility to your data integration and data analytics strategies. However, ELT sacrifices data quality, security, and compliance in many cases.

Because ETL and ELT present different strengths and weaknesses, many organizations are using a hybrid “ETLT” approach to get the best of both worlds. In this guide, we’ll help you understand the “why, what, and how” of ETLT, so you can determine if it’s right for your use-case.

#data science #data #data security #data integration #etl #data warehouse #data breach #elt #bid data

Gerhard  Brink

Gerhard Brink

1620629020

Getting Started With Data Lakes

Frameworks for Efficient Enterprise Analytics

The opportunities big data offers also come with very real challenges that many organizations are facing today. Often, it’s finding the most cost-effective, scalable way to store and process boundless volumes of data in multiple formats that come from a growing number of sources. Then organizations need the analytical capabilities and flexibility to turn this data into insights that can meet their specific business objectives.

This Refcard dives into how a data lake helps tackle these challenges at both ends — from its enhanced architecture that’s designed for efficient data ingestion, storage, and management to its advanced analytics functionality and performance flexibility. You’ll also explore key benefits and common use cases.

Introduction

As technology continues to evolve with new data sources, such as IoT sensors and social media churning out large volumes of data, there has never been a better time to discuss the possibilities and challenges of managing such data for varying analytical insights. In this Refcard, we dig deep into how data lakes solve the problem of storing and processing enormous amounts of data. While doing so, we also explore the benefits of data lakes, their use cases, and how they differ from data warehouses (DWHs).


This is a preview of the Getting Started With Data Lakes Refcard. To read the entire Refcard, please download the PDF from the link above.

#big data #data analytics #data analysis #business analytics #data warehouse #data storage #data lake #data lake architecture #data lake governance #data lake management

Virgil  Hagenes

Virgil Hagenes

1602702000

Data Quality Testing Skills Needed For Data Integration Projects

The impulse to cut project costs is often strong, especially in the final delivery phase of data integration and data migration projects. At this late phase of the project, a common mistake is to delegate testing responsibilities to resources with limited business and data testing skills.

Data integrations are at the core of data warehousing, data migration, data synchronization, and data consolidation projects.

In the past, most data integration projects involved data stored in databases. Today, it’s essential for organizations to also integrate their database or structured data with data from documents, e-mails, log files, websites, social media, audio, and video files.

Using data warehousing as an example, Figure 1 illustrates the primary checkpoints (testing points) in an end-to-end data quality testing process. Shown are points at which data (as it’s extracted, transformed, aggregated, consolidated, etc.) should be verified – that is, extracting source data, transforming source data for loads into target databases, aggregating data for loads into data marts, and more.

Only after data owners and all other stakeholders confirm that data integration was successful can the whole process be considered complete and ready for production.

#big data #data integration #data governance #data validation #data accuracy #data warehouse testing #etl testing #data integrations

Riley Lambert

Riley Lambert

1550559713

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