People

Blended Families Report¶

This SQL report can be added to the blue Toolbar so that you can run it for a Tag, an Organization, or Search Builder results. The script will look through the families represented to find any families in which any member has a different last name.

Create the Blended Families Script¶

Step 1

Go to Administration > Special Content > SQL Content and click the green + New Sql Script File button.

Enter the name BlendedFamilies (no space) and click Submit.

Step 2

Copy the code below and paste it into the file.

Then click the blue Save Sql Script button.

Step 3

Now click Run Sql Script (at the top of the file).

You will not have any data that first time, however, this gives you the option to add the report to the blue Toolbar. After it runs, select Add Report to Menu.

This will cause it to display under the code icon wherever you see the blue Toolbar.

Code¶

SELECT
    p.PeopleId,
    p.FamilyId,
    Name = p.Name2,
    Position = fp.[Description],
    Gender = g.[Description],
    p.Age
FROM dbo.People p
JOIN lookup.FamilyPosition fp ON fp.Id = p.PositionInFamilyId
JOIN lookup.Gender g ON g.Id = p.GenderId
JOIN dbo.TagPerson tp ON tp.PeopleId = p.PeopleId AND tp.Id = @qtagid
WHERE EXISTS(
    SELECT NULL
    FROM dbo.People fp
    JOIN dbo.Families f ON f.FamilyId = fp.FamilyId
    JOIN dbo.People hh ON hh.PeopleId = f.HeadOfHouseholdId
    WHERE fp.FamilyId = p.FamilyId
    AND hh.LastName NOT IN (fp.LastName, fp.MaidenName)
    AND fp.PositionInFamilyId in (10, 30)
)
ORDER BY p.FamilyId, p.PositionInFamilyId, p.GenderId, p.Name2