Add HeadofHouseholdId to Group in database

Add HeadofHouseholdId to Group in database


Right now this is inefficient to do in SQL because it does a row-level lookup


SELECT TOP 1 p.[Id] 


        [Person] p

        INNER JOIN [GroupMember] gm ON gm.[PersonId] = p.[Id]

        INNER JOIN [GroupTypeRole] gtr ON gtr.[Id] = gm.[GroupRoleId]


        gtr.[Guid] = '2639F9A5-2AAE-4E48-A8C3-4FFE86681E42' -- adult

        AND gm.[GroupId] = @UnitId

       ORDER BY p.[Gender]


And queries that need a HeadofHousehold could go much faster with that Id written to a table. Doesn't have to the Group table, just any table that relates Groups and HeadofHousehold

Photo of Josh CrewsSubmitted by Josh Crews, Simple Donation  ·   ·  Reporting
Login to add a comment...

  • Jon Edmiston

    You might look at the AnalyticsSourceGivingUnit table in v12.5. Depending on your use case it should allow you to get what you need.

    Adding this to the Group table would be difficult as the Group table is very abstract and used for lots of different use-cases. I could see adding a LeaderPersonAliasId, but the recipe for that calculation would be different for every group type.

    Hoping that the AnalyticsSourceGivingUnit will solve your need.