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
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.
--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
Modify image link with secure protocol.