Variations

For a simpler version using the past seven 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