Top 50 Donors
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
Filtered 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 - You can use 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
Filtered 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 - You can include the Distribution that 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
Filtered 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 - You can use a join to include the Program that determines the Statement Type, and filter for the 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
You can change the code to consider Soft Credit Donations in your calculations:
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