This View displays the 50 Donors who have given the most.

• Difficulty: Advanced
• Purpose: To Identify Donors who have given the most.
• Page: Donors

To create a View that lists all the top Donors, the following SQL may be used for the View Clause (filter criteria). This does not account for which Program or time-frame (we'll expand on this for later examples). It uses the Configuration Settings to omit anonymous Donors:

Donors.Donor_ID IN (
  SELECT TOP 50 D.Donor_ID FROM Donations D 
  WHERE D.Donor_ID NOT IN (SELECT CONVERT(int,CS.Value) 
    FROM dp_Configuration_Settings CS WHERE ISNUMERIC(CS.Value) = 1 
    AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID') 
    AND CS.Application_Code = 'COMMON' 
    AND CS.Domain_ID = D.Domain_ID) 
  GROUP BY D.Donor_ID 
  ORDER BY SUM(D.Donation_Amount) DESC
)

Variations

To change the number of Donors displayed, you can change the integer in the TOP statement:

SELECT TOP 100 D.Donor_ID FROM Donations D

Additional Fields

This View initially uses the fields from the Donors page. To add fields, you may want to copy the fields from the page and add the following fields:

Total Donations

For a total donations field, you can add this field. Be sure to add a comma to separate this from existing fields:

(SELECT SUM(D.Donation_Amount) FROM Donations D WHERE D.Donor_ID = Donors.Donor_ID) AS Donation_Total

If you include the total field, adding a sort order is a nice touch:

Donation_Total DESC

Filter by Date Range

To alter this View to use Donations for a particular time period, you can compare the Donation_Date. If you include the "Total Donations" field, it should include the same calculation for date as the View Clause.

View Clause — The highlighted line can be replaced with various date filters. For our example, we are using Donations in the prior calendar year:

Donors.Donor_ID IN (
  SELECT TOP 50 D.Donor_ID FROM Donations D 
  WHERE D.Donor_ID NOT IN (SELECT CONVERT(int,CS.Value) 
      FROM dp_Configuration_Settings CS WHERE ISNUMERIC(CS.Value) = 1 
      AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID') 
      AND CS.Application_Code = 'COMMON' 
      AND CS.Domain_ID = D.Domain_ID)
    AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 
  GROUP BY D.Donor_ID 
  ORDER BY SUM(D.Donation_Amount) DESC
)

Total Field — the filter for date must match the View filter for date.

(SELECT SUM(D.Donation_Amount) FROM Donations D 
  WHERE D.Donor_ID = Donors.Donor_ID
    AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 
) AS Donation_Total

Filter by Program

To alter this View to show giving for a Program, a join must be added for Donation Distribution and the amount calculated from the Distribution rather than the Donation:

View Clause — The highlighted line includes the Distribution which determines the Program:

Donors.Donor_ID IN (
  SELECT TOP 50 D.Donor_ID FROM Donations D 
  JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID 
  WHERE D.Donor_ID NOT IN (SELECT CONVERT(int,CS.Value) 
      FROM dp_Configuration_Settings CS WHERE ISNUMERIC(CS.Value) = 1 
      AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID') 
      AND CS.Application_Code = 'COMMON' 
      AND CS.Domain_ID = D.Domain_ID)
    AND DD.Program_ID = 3 
    AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 
  GROUP BY D.Donor_ID 
  ORDER BY SUM(DD.Amount) DESC
)

Total Field — the Field filter must match the View filter:

(SELECT SUM(DD.Amount) FROM Donations D 
  JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID 
  WHERE D.Donor_ID = Donors.Donor_ID 
    AND DD.Program_ID = 3 
    AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 
) AS Donation_Total

Filter by Statement Type

To alter this View to show giving for a Statement Type (such as Tithes), a join must be added for Program.

View Clause — The first highlighted join includes the Program which determines the Statement Type. The second highlighted line shows the filter for Statement Type. Program ID is omitted:

Donors.Donor_ID IN (
  SELECT TOP 50 D.Donor_ID FROM Donations D 
  JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID 
  JOIN Programs Prog ON DD.Program_ID = Prog.Program_ID 
  WHERE D.Donor_ID NOT IN (SELECT CONVERT(int,CS.Value) 
      FROM dp_Configuration_Settings CS WHERE ISNUMERIC(CS.Value) = 1 
      AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID') 
      AND CS.Application_Code = 'COMMON' 
      AND CS.Domain_ID = D.Domain_ID)
    AND Prog.Statement_Header_ID = 1  
    AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 
  GROUP BY D.Donor_ID 
  ORDER BY SUM(DD.Amount) DESC
)

Total Field — the Field filter must match the View filter:

(SELECT SUM(DD.Amount) FROM Donations D 
  JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID 
  JOIN Programs Prog ON DD.Program_ID = Prog.Program_ID 
  WHERE D.Donor_ID = Donors.Donor_ID 
   AND Prog.Statement_Header_ID = 1 
    AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 
) AS Donation_Total

Filter for Soft Credit

To consider Soft Credit Donations in your calculations, you need to change the following highlighted portions:

Donors.Donor_ID IN (
  SELECT TOP 50 ISNULL(DD.Soft_Credit_Donor,D.Donor_ID) FROM Donations D 
  JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID 
  JOIN Programs Prog ON DD.Program_ID = Prog.Program_ID 
  WHERE ISNULL(DD.Soft_Credit_Donor,D.Donor_ID)  NOT IN (SELECT CONVERT(int,CS.Value) 
      FROM dp_Configuration_Settings CS WHERE ISNUMERIC(CS.Value) = 1 
      AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID') 
      AND CS.Application_Code = 'COMMON' 
      AND CS.Domain_ID = D.Domain_ID)
    AND Prog.Statement_Header_ID = 1 
    AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 
  GROUP BY ISNULL(DD.Soft_Credit_Donor,D.Donor_ID) 
  ORDER BY SUM(DD.Amount) DESC
)

Total Field — the Field filter must match the View filter:

(SELECT SUM(DD.Amount) FROM Donations D 
  JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID 
  JOIN Programs Prog ON DD.Program_ID = Prog.Program_ID 
  WHERE ISNULL(DD.Soft_Credit_Donor,D.Donor_ID) = Donors.Donor_ID 
  AND Prog.Statement_Header_ID = 1 
    AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 
) AS Donation_Total

Techniques

The following SQL Functions were used in this example:

• Configuration Settings
• ID IN SELECT
• SELECT TOP N
• SUM
• GETDATE
• EXISTS
• ISNULL