July 22, 2015 at 11:48 am
Hello,
I have 4 different data sources I would like to compare. Basically I have a list of users from our Intranet, Active Directory, Phone System, and Cell Phone provider.
What I would like to do is compare all data to see if data is accurate across the board. One challege I have is their is no ID to tie each data source together. I plan on matching the best I can based on FirstName and LastName.
How can I create a query that will show all records for each file in one report and tie them together if the last and first names are identical? Do I need to do a UNION for this or is there a better way?
July 22, 2015 at 12:03 pm
If you're data is still in the source systems, I would suggest ETL'ing them into tables in a single database. Then you could easily do a SQL query like this:
select *
from AD_info a
left join cell_info b on a.firstname = b.firstname and a.lastname = b.lastname
left join ...
Hopefully you don't have too many John Smiths...
July 22, 2015 at 1:23 pm
SQL Hamster (7/22/2015)
If you're data is still in the source systems, I would suggest ETL'ing them into tables in a single database. Then you could easily do a SQL query like this:select *
from AD_info a
left join cell_info b on a.firstname = b.firstname and a.lastname = b.lastname
left join ...
Hopefully you don't have too many John Smiths...
Assuming they all end up in the same database, using a LEFT JOIN is not necessarily going to provide what's needed, as a full comparison needs to see ALL values, and not just those that come from any one given system. FULL OUTER JOIN is needed here, like this:
SELECT COALESCE(AD.LastName, INET.LastName, PH.LastName, CELL.LastName) AS LastName,
COALESCE(AD.FirstName, INET.FirstName, PH.FirstName, CELL.FirstName) AS FirstName,
CASE WHEN AD.LastName IS NULL THEN 0 ELSE 1 END AS AD,
CASE WHEN PH.LastName IS NULL THEN 0 ELSE 1 END AS PH,
CASE WHEN INET.LastName IS NULL THEN 0 ELSE 1 END AS INET,
CASE WHEN CELL.LastName IS NULL THEN 0 ELSE 1 END AS CELL,
FROM ADInfo AS AD
FULL OUTER JOIN IntranetInfo AS INET
ON AD.LastName = INET.LastName
AND AD.FirstName = INET.FirstName
FULL OUTER JOIN PhoneInfo AS PH
ON AD.LastName = PH.LastName
AND AD.FirstName = PH.FirstName
FULL OUTER JOIN CellProviderInfo AS CELL
ON AD.LastName = CELL.LastName
AND AD.FirstName = CELL.FirstName
ORDER BY COALESCE(AD.LastName, INET.LastName, PH.LastName, CELL.LastName) AS LastName,
COALESCE(AD.FirstName, INET.FirstName, PH.FirstName, CELL.FirstName) AS FirstName
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 22, 2015 at 2:08 pm
thank you for for your time and help!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply