New Member Giving¶

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.

Code¶

--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.