GivingForecast SQL Script ================================ .. admonition:: :doc:`View the Code` Suggestion: Right-click the link and select the "Open link in a new window" option then arrange your windows you can view side by side. 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. :doc:`GivingForecastDataPython` 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.