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.

SELECT
    p.PeopleId,
    p.FamilyId,
    Name = p.Name2,
    Address = f.AddressLineOne,
    p.Age
FROM dbo.People p
JOIN dbo.Families f ON f.FamilyId = p.FamilyId
WHERE f.AddressLineOne IS NOT NULL
--AND f.AddressLineOne <> 'N/A'
AND EXISTS(
    SELECT NULL
    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