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