Giving Units 365¶

This script looks at giving by Family Unit. It shows Totals by family for the past 365 days.

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

The recommended name is GivingUnits365

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

--Roles=Finance
DECLARE @td DATETIME = GETDATE()
DECLARE @fd DATETIME = DATEADD(DAY, -365, @td)
;WITH units AS (
    SELECT c.FamilyId
          ,SUM(c.Amount) Amount
          ,SUM(c.PledgeAmount) PledgeAmount
    FROM dbo.Contributions2(@fd, @td, 0, 0, NULL, 1, NULL) c
    GROUP BY c.FamilyId
)
SELECT
    p.PeopleId,
    p.SpouseId,
    p.Name2 GivingUnit,
    sp.PreferredName Spouse,
    units.Amount,
    units.PledgeAmount
FROM units
JOIN dbo.Families f ON f.FamilyId = units.FamilyId
JOIN dbo.People p ON p.PeopleId = f.HeadOfHouseholdId
LEFT JOIN dbo.People sp ON sp.PeopleId = p.SpouseId
ORDER BY p.Name2