What would you like to know more about?

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_Name

This 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