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
For a more advanced version using the previous calendar week, see New Donors Last Week.
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:
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]
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]
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
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
The following SQL Functions were used in this example: