Givers Over Amount Report
=========================

This SQL script will look for any donors who have given
more than the amount specified in the script during the current calendar year to date.
If couples give jointly, only the HOH will be listed in the report.
This report is protect by the user role Finance.
After you run the report for the first time,
you can opt to **Add to Menu**. If you click on that link,
this report will be listed under Reports in the Main Menu.
As with all of our SQL Scripts, you can download as Excel.

Copy the :ref:`code below <GiversOverAmountCode>` and paste it into your database after
creating the New Sql Script.
We suggest naming it `GiversOverAmount`.

See :doc:`../CreateSqlScript`.

Sample Report
-------------
For this sample report with test data, we used the amount of $10,000 in the script.
The code below has $20,000 as the amount. You can make it whatever amount you want.

.. figure:: https://i.tpsdb.com/2017-09-24_18-32-45.png
    :target: #

.. _GiversOverAmountCode:

.. code-block:: sql

    --Roles=Finance
    IF LEN(@p1) = 0
        SET @p1 = CONVERT(VARCHAR, DATEPART(yyyy, GETDATE()))
    SELECT
        PeopleId, Amount, Name, PrimaryAddress, PrimaryAddress2, PrimaryCity, PrimaryState, PrimaryZip
    FROM dbo.People p
    JOIN
    (
        SELECT
            CreditGiverId,
            SUM(Amount) Amount
        FROM Contributions2('1/1/' + @p1, '12/31/' + @p1, 0, 0, NULL, 0, NULL)
        GROUP BY CreditGiverId
    ) tt ON tt.CreditGiverId = p.PeopleId
    WHERE tt.Amount > 20000
    ORDER by 'Amount'

|
|

+--------------------+------------------+
| **Latest Update**  | **11/13/2020**   |
+--------------------+------------------+

Modify image link with secure protocol.