First Gifts in Past 365 Days¶

This SQL script can be added to the main Reports menu and will display every person whose first time to give to the church was in the past 365 days. The report contains the person’s name, PID# (which is a link to his people record), the date of the person’s first gift, and the total number of gifts in the 365 days. It is sorted by date with the most recent dates at the top of the report.

Like most reports you have the option to Download to Excel.

Sample Report¶

https://i.tpsdb.com/./2018-01-26_14-48-04.png

Note

Those gifts with a time included with the date are from online giving.

Create the First Gifts 365 Report¶

Step 1

Go to Administration > Setup > Special Content and select the SQL Scripts tab.

Step 2

Select the green + New SQL Script File button and enter the name as FirstGifts365 and press Submit.

Step 3

Select and copy all of the code below and paste it into the new file you created.

Press the blue Save SQL Script button at the bottom of the file.

Step 4

Now press Run Script. To add this report to the Toolbar, press the Add Report to Menu button at the bottom of the report.

--Roles=Finance

SELECT PeopleId,
    p.PreferredName,
    p.LastName,
    FirstGift FirstGiftLast365,
    tt.Cnt GiftCountLast365
FROM
(
    SELECT CreditGiverId, MIN(Date) AS FirstGift, COUNT(*)  Cnt
    FROM dbo.Contributions2('1/1/1900', GETDATE(), 0, 0, 0, 1, NULL) c
    GROUP BY CreditGiverId
) tt
JOIN dbo.People p ON p.PeopleId = tt.CreditGiverId
WHERE FirstGift > DATEADD(dd, -365, GETDATE())
ORDER BY tt.FirstGift DESC

Latest Update

11/13/2020

Modify image link with secure protocol.