People

Giving Snapshot by Quarters

This script can be added to the blue Toolbar and will be available on 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 displays giving totals by quarters for a three-year period for the designated fund. By default, the three-year period ends with the current year, but you can change the script so that the period ends with the year of your choice.

Important

The report shows giving for donor only. It does not combine family or couple giving. A use case for the report might be to show donor totals for an age range.

The recommended name is GivingSnapshotByQuarters

Use the code below to create the SQL Script.

See How to create a SQL Script.

The top declaration statements are where you can set/change the parameters for the report. Currently, the report is set for Fund ID = 1 and for the three-year report period to end with the current year. To change the Fund ID, enter the desired ID in line 4.

To change the report’s three-year period to end with a year other than the current year, add two dashes -- to the beginning of line 7 to comment it out and remove the two dashes at the beginning of line 9. Then enter the ending year within the single quote marks after the = sign. If the ending year is changed, the column headers for the yearly totals should also be changed. This can be on lines 173, 178, and 183 of the script.

SQL Code for the Report

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
--Roles=Finance

-- Enter the desired fund ID on the next line after the equal sign
DECLARE @fundId INT = 1
DECLARE @today DATE = CONVERT(DATE,GETDATE())
-- The next line sets the final of the three years to the current year
DECLARE @year3 INT = DATEPART(year,GETDATE())
-- To manually set the third (last) year, enter it on the next line after the equal sign (e.g., = '2019')
--DECLARE @year3 DATE = '2019'
DECLARE @year2 INT = @year3 - 1
DECLARE @year1 INT = @year3 - 2
DECLARE @fd NVARCHAR(10) = '1-1-' + CONVERT(NVARCHAR(4),@year1)

;WITH gifts AS (
SELECT datepart(month,Date) as Month
,datepart(quarter,Date) as Quarter
,datepart(year,Date) as Year
,c.PeopleId
,c.FundId
,c.Amount
FROM dbo.Contributions2(@fd,@today, 0, 0, NULL, 1, NULL) c
)
,y1q1 AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year1
    AND Quarter = 1
    GROUP BY PeopleId
)
,y1q2 AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year1
    AND Quarter = 2
    GROUP BY PeopleId
)
,y1q3 AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year1
    AND Quarter = 3
    GROUP BY PeopleId
)
,y1q4 AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year1
    AND Quarter = 4
    GROUP BY PeopleId
)
,y1all AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year1
    GROUP BY PeopleId
)
,y2q1 AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year2
    AND Quarter = 1
    GROUP BY PeopleId
)
,y2q2 AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year2
    AND Quarter = 2
    GROUP BY PeopleId
)
,y2q3 AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year2
    AND Quarter = 3
    GROUP BY PeopleId
)
,y2q4 AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year2
    AND Quarter = 4
    GROUP BY PeopleId
)
,y2all AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year2
    GROUP BY PeopleId
)
,y3q1 AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year3
    AND Quarter = 1
    GROUP BY PeopleId
)
,y3q2 AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year3
    AND Quarter = 2
    GROUP BY PeopleId
)
,y3q3 AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year3
    AND Quarter = 3
    GROUP BY PeopleId
)
,y3q4 AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year3
    AND Quarter = 4
    GROUP BY PeopleId
)
,y3all AS (
    SELECT PeopleId
    , SUM(Amount) as Amount
    FROM gifts
    WHERE FundId = @fundId
    AND Year = @year3
    GROUP BY PeopleId
)
,recurgivers AS (
SELECT amt.PeopleId
    FROM dbo.RecurringAmounts amt
    JOIN dbo.ManagedGiving schedule
    ON amt.peopleid = schedule.peopleid
    WHERE amt.fundid = @fundId
    AND amt.Amt > 0
)
SELECT
    p.PeopleId,
    p.Name2 as Name,
    mem.Description AS "Member Status",
    c.Description AS "Campus",
    CASE WHEN recur.PeopleId IS NOT NULL THEN 'True' ELSE '' END AS "Has Recurring",
    y1q1.Amount AS "Y1Q1",
    y1q2.Amount AS "Y1Q2",
    y1q3.Amount AS "Y1Q3",
    y1q4.Amount AS "Y1Q4",
    y1all.Amount AS "2YO TOTAL",
    y2q1.Amount AS "Y2Q1",
    y2q2.Amount AS "Y2Q2",
    y2q3.Amount AS "Y2Q3",
    y2q4.Amount AS "Y2Q4",
    y2all.Amount AS "LAST YEAR TOTAL",
    y3q1.Amount AS "Y3Q1",
    y3q2.Amount AS "Y3Q2",
    y3q3.Amount AS "Y3Q3",
    y3q4.Amount AS "Y3Q4",
    y3all.Amount AS "CURRENT YEAR TOTAL"
FROM dbo.People p
LEFT JOIN lookup.Campus c on p.CampusId = c.Id
JOIN lookup.MemberStatus mem on p.MemberStatusId = mem.Id
LEFT JOIN y1q1 ON p.PeopleId = y1q1.PeopleId
LEFT JOIN y1q2 ON p.PeopleId = y1q2.PeopleId
LEFT JOIN y1q3 ON p.PeopleId = y1q3.PeopleId
LEFT JOIN y1q4 ON p.PeopleId = y1q4.PeopleId
LEFT JOIN y1all ON p.PeopleId = y1all.PeopleId
LEFT JOIN y2q1 ON p.PeopleId = y2q1.PeopleId
LEFT JOIN y2q2 ON p.PeopleId = y2q2.PeopleId
LEFT JOIN y2q3 ON p.PeopleId = y2q3.PeopleId
LEFT JOIN y2q4 ON p.PeopleId = y2q4.PeopleId
LEFT JOIN y2all ON p.PeopleId = y2all.PeopleId
LEFT JOIN y3q1 ON p.PeopleId = y3q1.PeopleId
LEFT JOIN y3q2 ON p.PeopleId = y3q2.PeopleId
LEFT JOIN y3q3 ON p.PeopleId = y3q3.PeopleId
LEFT JOIN y3q4 ON p.PeopleId = y3q4.PeopleId
LEFT JOIN y3all ON p.PeopleId = y3all.PeopleId
LEFT JOIN recurgivers recur ON p.PeopleId = recur.PeopleId
JOIN dbo.TagPerson tp ON tp.PeopleId = p.PeopleId AND tp.Id = @qtagid
ORDER BY p.PeopleId


Latest Update

07/06/2020

Added this article.