Giving Units (Date Range)¶

This SQL report looks at a giving unit as either an donor that gives individually or a couple that gives jointly and will present the total amount given during the date range. The report will sort by the last name and has the following columns:

• Name2 (last, then first)

• IsDeceased (true or false)

• PeopleId

• Spouse (name)

• SpouseID

• Total

• FamilyId

• ContributionOptionsId (This is actually the Statement Type: 1=Individual; 2=Joint; 9=None)

• PositionInFamilyId (10=Primary Adult; 20=Secondary Adult; 30=Child)

This report uses a date range that is inside the script itself (lines 3 & 4). Because of that fact you may want to always run the report from Special Content instead of adding it to Reports in the Main Menu. This way you will be reminded of the date range in the script and can change it if necessary.

You may also be interested in the GivingUnits365 report. This is similar to this report, but it looks back 365 days from the current date. Click here to see that SQL recipe

Use the following code to Create the SQL Script. The recommended name of the script is GivingUnits. See How to create a SQL Script.

--Roles=Finance
DECLARE
    @startdt DATETIME = '1/1/17'
    , @enddt DATETIME = '12/1/17'
    , @campusid INT = 0
    , @pledges INT = 0
    , @nontaxded INT = 0
    , @includeUnclosed INT = 1

;WITH givers AS (
    SELECT
        FamilyId
        , CreditGiverId
        , SpouseId
        , SUM(Amount) AS Total
    FROM Contributions2(@startdt, @enddt, @campusid, @pledges, @nontaxded, @includeUnclosed, NULL)
    GROUP BY FamilyId, CreditGiverId, SpouseId
)
SELECT
    p.Name2
    ,p.IsDeceased
    ,p.PeopleId
    ,sp.FirstName Spouse
    ,sp.PeopleId SpouseID
    ,givers.Total
    ,givers.FamilyId
    ,p.ContributionOptionsId
    ,p.PositionInFamilyId

FROM givers
JOIN dbo.People p ON p.PeopleId = givers.CreditGiverId
LEFT JOIN dbo.People sp ON sp.PeopleId = givers.SpouseId
WHERE givers.Total > 1
AND p.IsDeceased = 0
ORDER BY p.Name2