This View shows both Heads of Household and their Household giving for this and last year.

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