This View shows both Heads of Household and their Household giving for this and last year.
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).
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]
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)