This View displays Donors who have given for the first time in the past 7 days.

To create a View that lists all the first-time donations last calendar week, the following SQL may be used for criteria.

The SQL makes use of the GETDATE Function to find new donations in the last 7 days ago:

_First_Donation_Date > GETDATE()-7

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 highlighted 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