New Member Giving¶
This SQL script will find all new members looking back the declared number of days and list their total giving during that time period and their age. This is not combining couples; it merely lists whichever person on whose record the giving was recorded. It does list all new members. If they have no giving, it displays $0.00
Create New Member Giving Script¶
- Step 1
Go to Administration > Setup > Special Content > SQL Scripts and press the green + New Sql Script File button.
Enter the name as NewMemberGiving and press the Submit button.
- Step 2
Select and copy the entire code list below and paste it into the new file you created.
This code is declaring 90 days as the time period to look for new church members. You can change that to whatever number of days you want to look back.
Also, the code is looking only at Primary Adults. See line 20.
Press the blue Save Sql Script button at the bottom of the file.
- Step 3
- Now click the Run Script button at the top of the file and then click the Add Report to Menu link and the bottom of the report. This will add the report to the main Reports menu.
--Roles=Finance DECLARE @days INT = 90 DECLARE @dt2 DATETIME = GETDATE() DECLARE @dt1 DATETIME = DATEADD(DAY, -@days, @dt2) ; WITH givers AS ( SELECT Name2 , ( SELECT SUM(c.ContributionAmount) FROM dbo.Contribution c WHERE c.PeopleId = p.PeopleId AND c.ContributionDate >= @dt1 AND c.ContributionDate < @dt2 ) Total , Age FROM People p WHERE p.JoinDate >= @dt1 AND p.JoinDate < @dt2 AND p.PositionInFamilyId = 10 ) SELECT givers.Name2 ,Total = ISNULL(givers.Total, 0) ,givers.Age FROM givers ORDER BY givers.Total DESC, givers.Age DESC
Modify image link with secure protocol.