Variations

For a more advanced version using the previous calendar week, see New Donors Last Week.

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(Do.Donation_Amount) FROM Donations Do WHERE Do.Donor_ID = Donors.Donor_ID) AS [Total Donations]

Distribution List

To show a list of Programs the Donations have been distributed to, you can add the following field. Be sure to add a comma to separate this from existing fields. Whitespace is optional:

, STUFF((SELECT ', ' + P.Program_Name  
FROM Donation_Distributions DD   
JOIN Programs P ON DD.Program_ID = P.Program_ID  
JOIN Donations D ON DD.Donation_ID = D.Donation_ID  
WHERE D.Donor_ID = Donors.Donor_ID  
FOR XML PATH('')), 1, 2, '') AS [Distributions]

Filter by Giving Amount

To alter this View to show only first-time Donors that have given a minimum amount, you can add this to the criteria. Use "AND" to combine with existing criteria. Change the amount to suit your needs:

(SELECT SUM(Do.Donation_Amount) FROM Donations Do WHERE Do.Donor_ID = Donors.Donor_ID) > 5

Filter by Household Giving

To alter this View to show only first-time Donors for a Household, you can change the filter to this (whitespace is optional):

EXISTS(SELECT 1 FROM Donors D JOIN Contacts C ON D.Contact_ID = C.Contact_ID  WHERE D.Donor_ID = Donors.Donor_ID AND C.Household_ID IN    (SELECT C2.Household_ID FROM Donors D2 JOIN Contacts C2 ON D2.Contact_ID = C2.Contact_ID    GROUP BY C2.Household_ID HAVING MIN(D2._First_Donation_Date) > GETDATE()-7 ))AND Donors._First_Donation_Date IS NOT NULL

Techniques

The following SQL Functions were used in this example:

• GETDATE
• EXISTS
• STUFF