This View displays Donors who have given for the first time in the previous calendar week.

To create this View, use the following SQL for criteria:

DATEPART(wk, _First_Donation_Date) = DATEPART(wk, GETDATE()-7) AND _First_Donation_Date > GETDATE()-14

Variations

For a simpler version using the past 7 days, see New Donors Last 7 Days.

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 DATEPART(wk, MIN(D2._First_Donation_Date)) = DATEPART(wk, GETDATE()-7)
      AND MIN(D2._First_Donation_Date) > GETDATE()-14 )
)
AND Donors._First_Donation_Date IS NOT NULL

Techniques

The following SQL Functions were used in this example and variations:

• GETDATE
• DATEPART
• EXISTS
• STUFF
• MIN