This script can be added to the blue Toolbar and will be available or Search Builder results, an Organization, or a Tag. If you run it from Special Content, the report will look at the last Search Builder results and runs the script for those individuals.
It compares two periods of time. The default is a 365-day period.
- The first period will be starting at 365 * 2 or 730 days ago through 365 days ago.
- The second period will be starting 365 days ago through today.
The recommended name is
Use the code below to create the SQL Script.
The top declaration statements are where you can set/change the parameters for the report. Currently, the report is set for the minimum change percent to be 30%, the minimum dollar amount is set at $500, and the period is set as 365 days. Of course, you can set other Conditions in Search Builder when you run the report from the Toolber there.
We highly recommend that you use a range of at least 365 days. Looking at a smaller range, you will find people whose giving has decreased, for example, because they give only once a year or quarterly.
--Roles=Finance DECLARE @MinumumPctChange NUMERIC = 30; DECLARE @MinimumTotal NUMERIC = 500; DECLARE @PeriodDays INT = 365; SELECT Name ,gc.PeopleId ,gc.TotalPeriod1 ,gc.TotalPeriod2 ,gc.Change FROM GivingChange(@PeriodDays) gc JOIN dbo.People p ON p.PeopleId = gc.PeopleId WHERE gc.TotalPeriod1 + gc.TotalPeriod2 >= @MinimumTotal AND ABS(gc.PctChange) > @MinumumPctChange ORDER BY gc.PctChange ,( gc.TotalPeriod2 - gc.TotalPeriod1 );
Notice in the sample report that some have Stopped giving, others have Increased or Decreased and the percent of change, and still others have Started giving.