问题描述:

I am currently trying to audit our systems for invalid information. I want to compare companies_address against my xro_zips table that has City, State, Zipcode.

I can return some zip codes that are not matching up between these two tables, but I would also like to return incorrect city names and state abbreviations where the zip codes match between the two tables.

This is what I have so far..

SELECT DISTINCT 'workplace_base' as [db_id], c.external_id, c.company, ca.city,

ISNULL(ca.[state],'') as [state], ISNULL(ca.zip_code,'') as zip_code, d.division

FROM Workplace_base.dbo.companies_address ca

INNER JOIN Workplace_base.dbo.companies c ON ca.company_id = c.company_id

INNER JOIN Workplace_base.dbo.divisions d ON c.owner_division_id = d.division_id

LEFT JOIN Workplace_base.dbo.xro_zips z ON ca.zip_code = z.ZipCode

and ca.[state] = z.[State]

WHERE z.ZipCode IS NULL

Any help would be great. Thank you ahead of time.

网友答案:

This sort of thing should give all discrepancies:

SELECT * FROM (
SELECT DISTINCT 
    'workplace_base' as [db_id], 
    c.external_id, 
    c.company, 
    ca.city AS City1, 
    z.City AS City2, 
    ISNULL(ca.[state],'') as [State1], 
    ISNULL(z.[State],'') as [State2], 
    ca.zip_code as zip_code1,
    z.ZipCode as zip_code2,
    ISNULL(ca.zip_code,'Missing ZIP') as zip_code_check1, 
    ISNULL(z.ZipCode,'Extra ZIP') as zip_code_check2, 
    d.division,
    CASE WHEN ca.city <> z.city THEN 'City Mismatch' ELSE 'OK' END AS CityCheck,    
    CASE WHEN ca.[state] <> z.[state] THEN 'State Mismatch' ELSE 'OK' END AS StateCheck,    
FROM Workplace_base.dbo.companies_address ca
INNER JOIN Workplace_base.dbo.companies c ON ca.company_id = c.company_id
INNER JOIN Workplace_base.dbo.divisions d ON c.owner_division_id = d.division_id
FULL OUTER JOIN Workplace_base.dbo.xro_zips z ON ca.zip_code = z.ZipCode
) AS Comparison
WHERE zip_code1 IS NULL or zip_code2 IS NULL OR CityCheck <> 'OK' OR StateCheck <> 'OK'
相关阅读:
Top