Family With Duplicate Addresses

This script runs from the Main Menu if added to the Menu. It shows heads of households with a family address that is the same as in another family. It could simply mean that two families live under the same roof, but likely it is an out of date address.

The report is ordered by address so you can see the address pairs easily.

If you have addresses that you want to exclude, you can uncomment the line --AND f.AddressLineOne <> 'N/A' and change it to fit your requirements.

The recommended name is FamilyWithDuplicateAddresses

Use the following code to Create the SQL Script. See How to create a SQL Script.

    Name = p.Name2,
    Address = f.AddressLineOne,
FROM dbo.People p
JOIN dbo.Families f ON f.FamilyId = p.FamilyId
WHERE f.AddressLineOne IS NOT NULL
--AND f.AddressLineOne <> 'N/A'
    FROM dbo.People fp
    JOIN dbo.Families ff ON ff.FamilyId = fp.FamilyId
    WHERE ff.AddressLineOne = f.AddressLineOne
    AND fp.FamilyId <> p.FamilyId
    AND fp.PeopleId = ff.HeadOfHouseholdId
AND f.HeadOfHouseholdId = p.PeopleId
ORDER BY f.AddressLineOne