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