GivingHouseholds SQL Script¶
Suggestion: Right-click the link and select the “open link in a new window” item 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
Lines 3-5 declare the names of the QueryTags used to define Members, NonMembers and Both
Lines 7-10 create a temporary table called #contributions containing the columns needed to produce the analysis data.
The contributions are limited to FundId 180 and a date range passed in as parameters, @StartDt and @EndDt.
Lines 12-18 create an inline view called Members
using the GivingHousehold-Members QueryTag to get a list of PeopleId and FamilyId of Members.
PeopleId relates the Person table to the #contributions table.
FamilyId is used to get family counts.
The GivingHouseholdsData Python Script creates the three QueryTags.
Lines 20-26 create an inline view called NonMembers
using the GivingHouseholds-ActiveNonMembers QueryTag to do the same as the previous view.
Lines 29-34 create an inline view called Both containing Members and NonMembers
using the GivingHouselds-Combined QueryTag
Lines 36-68 fetch three separate rows of family counts and giving amounts for Members, NonMembers, and Combined.
The first three columns are Cnt (giving families), Amount (total giving), and Households (all families).
The last three columns (Tag, StartDt, EndDt) are needed to construct reconciliation report links.