Top Giving Families¶

This script runs from the Main Menu if added to the Menu It shows the top 100 Family Giving Units with the amounts for the past 365 days all funds.

The comment at the top of the script ensures that this report can only be run by a Finance user.

The recommended name is TopGivingFamilies

Use the following code to Create the SQL Script. See How to create a SQL Script.

--Roles=Finance
DECLARE @topn INT = 100
DECLARE @fundid INT = NULL -- All funds
DECLARE @days INT = 365
DECLARE @td DATETIME = GETDATE()
DECLARE @fd DATETIME = DATEADD(DAY, -@days, @td)
;WITH families AS (
    SELECT c.FamilyId
           ,SUM(c.Amount) Amount
    FROM     dbo.Contributions2(@fd, @td, 0, 1, NULL, 1, NULL) c
    WHERE ISNULL(@fundid, 0) = 0 OR c.FundId = @fundid
    GROUP BY c.FamilyId
),
topgivers AS (
    SELECT TOP(@topn)
        f.FamilyId
        ,f.Amount
    FROM families f
    ORDER BY f.Amount DESC
)
SELECT
   f.FamilyId
   ,p.PeopleId
   ,p.SpouseId
   ,p.Name2 Head
   ,sp.PreferredName Spouse
   ,g.Amount
FROM topgivers g
JOIN dbo.Families f ON f.FamilyId = g.FamilyId
JOIN dbo.People p ON p.FamilyId = g.FamilyId AND p.PeopleId = f.HeadOfHouseholdId
LEFT JOIN dbo.People sp ON sp.PeopleId = p.SpouseId
ORDER BY g.Amount DESC