home

MinistryPlatform

RealmRealm AccountingMinistryPlatformeGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite
RealmRealm AccountingMinistryPlatformeGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite

What would you like to know more about?

Show Contents

thumbnailMinistryPlatform

View Examples

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
MinistryPlatform

Last updated: July 12, 2024