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 :doc:`../CreateSqlScript`.

.. code-block:: sql

    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