Household Donations
This View shows both Heads of Household and their Household giving for this and last year.
Difficulty: Advanced
Purpose: Creates a Field with a List of all associated Heads of Households, and their giving.
Page: Household
Field List
Heads of Household Fields
Here is the Household name followed by two fields which display Heads of Household:
Households.[Household_Name] AS [Household Name],(SELECT TOP 1 First_Name FROM Contacts C WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 ORDER BY Gender_ID) AS Head_1_Name,(SELECT TOP 1 First_Name FROM Contacts C WHERE C.Household_ID = Households.Household_ID AND Household_Position_ID = 1 AND EXISTS (SELECT 1 FROM Contacts C2 WHERE C2.Household_Position_ID = 1 AND C2.Household_ID = C.Household_ID GROUP BY Household_ID HAVING Count(*) > 1) GROUP BY Contact_ID, First_Name, Gender_ID ORDER BY Gender_ID DESC) AS Head_2_NameThis can be followed by other Household details (which are omitted here).
Giving Totals
Here are three fields which display Prior Year Giving, Current Year Giving, and the difference:
,(SELECT SUM(Do.Donation_Amount) FROM Contacts C
JOIN Donations Do ON Do.Donor_ID = C.Donor_Record
WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1
AND YEAR(Do.Donation_Date) = YEAR(GETDATE())-1) AS [Prior Year Giving],(SELECT SUM(Do.Donation_Amount) FROM Contacts C JOIN Donations Do ON Do.Donor_ID = C.Donor_Record WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 AND YEAR(Do.Donation_Date) = YEAR(GETDATE())) AS [Current Year Giving] ,((SELECT SUM(Do.Donation_Amount) FROM Contacts C JOIN Donations Do ON Do.Donor_ID = C.Donor_Record WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 AND YEAR(Do.Donation_Date) = YEAR(GETDATE())-1) - (SELECT SUM(Do.Donation_Amount) FROM Contacts C JOIN Donations Do ON Do.Donor_ID = C.Donor_Record WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 AND YEAR(Do.Donation_Date) = YEAR(GETDATE()))) AS [Giving Difference]View Clause
To limit the list to only those Households which have given in the current or previous year, you can add the following criteria:
EXISTS(SELECT 1 FROM Contacts C JOIN Donations Do ON Do.Donor_ID = C.Donor_Record WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 AND YEAR(Do.Donation_Date) >= YEAR(GETDATE())-1)Techniques
- GETDATE SQL Function
- ISNULL SQL Function
- EXISTS SQL Function
- SELECT TOP 1
- SUM SQL Function