New Donors Last Week
This View displays Donors who have given for the first time in the previous calendar week.
Difficulty: Advanced
Purpose: To Identify Donors who have given for the first time in the prior calendar week.
Page: Donors
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 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 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