GivingForecast SQL Script¶

Explanation of the Code¶

Line 1 limits direct viewing of the data to Finance role

Line 3 Only the recurring giving to the Main fund (180) is analyzed.

Lines 8-14 uses the GivingForecast-Members QueryTag to create an inline view of PeopleId and FamilyId for Members. PeopleId relates the Person table to the RecurringAmounts table. FamilyId is used to get family counts. GivingForecastData Python Script Creates the three QueryTags

Lines 16-22 uses the GivingForecast-ActiveNonMembers QueryTag to do the same as previous.

Lines 24-29 combines Members and NonMembers into a view with an extra column called MemberStatus.

Lines 31-42 creates a view returning two summary rows showing family counts (FamilyCnt) of Members and NonMembers.

Lines 44-48 creates a view called RecurTable of PeopleIds for any family member having recurring giving.

Lines 50-63 creates a view returning two summary rows called RecurringCounts for both Members and NonMembers who have recurring gifts. The result has the family count (called RecurringCnt) for each family having at least one recurring giving member.

Lines 65-114 creates an inline view of the Recurring amounts for each different schedule (Monthly, Weekly, BiWeekly, SemiMonthly). The view normalizes the amounts to monthly using the appropriate multiplier.

Lines 116-121 creates a view called Totals returning two rows of the total normalized monthly amount for Members and NonMembers.

Lines 123-131 produces the final summarized data table for Members and NonMembers. The statement uses the group by rollup SQL construct to produce a third row for the Combined total.