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

.. code-block:: sql

    --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