Last Attend Last Gift 365¶

This script will find everyone who has attended anything in the past 365 days or has made a contribution in the past 365 days. The report can be added to Reports in the Main Menu.

If you want to look back for a shorter time period, you can change 365 on lines 9 and 21 to 30 or 60 or however many days you want to look back from today.

Because this has financial information in the report, it is protected and only users with Finance role can see it. You have the option to Download as Excel after running the report.

Create the Report¶

Copy the code below and paste it into a new SQL Script in your database. The recommended name is LastAttendLastGift365.

See How to create a SQL Script.

Sample Report¶

Notice that some people in the report have only a date for a last gift and not a date of having attended in the past 365 days. Others have only attendance and no giving, and still others have both. Those with attendnce will also have a count for the number of times they attended anything in the past 365 days.

https://i.tpsdb.com/2017-09-25_18-23-54-1.png

    --Roles=Finance
;WITH attends AS (
    SELECT
        a.PeopleId,
        MAX(MeetingDate) LastAttendDt,
        COUNT(*) [Count]
    FROM dbo.Attend a
    WHERE a.AttendanceFlag = 1
    AND a.MeetingDate > DATEADD(DAY, -365, GETDATE())
    GROUP BY a.PeopleId
    HAVING COUNT(*) > 1
),
gifts AS (
    SELECT
        c.PeopleId,
        MAX(c.ContributionDate) LastGiftDt
    FROM dbo.Contribution c
    WHERE c.ContributionStatusId = 0
    AND c.ContributionAmount > 0
    AND ISNULL(c.PledgeFlag, 0) = 0
    AND c.ContributionDate > DATEADD(DAY, -365, GETDATE())
    GROUP BY c.PeopleId
)
SELECT
    p.PeopleId,
    p.Name2,
    g.LastGiftDt,
    a.LastAttendDt,
    a.[Count]
FROM dbo.People p
LEFT JOIN attends a ON a.PeopleId = p.PeopleId
LEFT JOIN gifts g ON g.PeopleId = p.PeopleId
WHERE a.LastAttendDt IS NOT NULL OR g.LastGiftDt IS NOT NULL
ORDER BY Name2

Latest Update

11/13/2020

Modify image link with secure protocol.