GivingHouseholds SQL Script¶

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.