Building a query for gift in at least two of the last five years

Laura Neidhold

Curious all of your thoughts on the best way to structure a query to find all constituents who have given a gift in at least 2 of the last 5 fiscal years. The best way that I've been able to come up is -

(Gift Date Between 01-01-2020 and 12-31-2020 AND Last Gift Date greater than or equal to 01-01-2021)

OR (Gift Date Between 01-01-2021 and 12-31-2021 AND Last Gift Date greater than or equal to 01-01-2022).

OR (Gift Date Between 01-01-2022 and 12-31-2022 AND Last Gift Date greater than or equal to 01-01-2023).

OR (Gift Date Between 01-01-2023 and 12-31-2023 AND Last Gift Date greater than or equal to 01-01-2024).

Dan Snyder

@Laura Neidhold That looks good to me, not sure you could get anything better without having to export

Dariel Dixon

@Laura Neidhold This looks fine. I depending on your needs, I might use summary fields if I needed to exclude pledges and only include payments. Especially if unpaid annual pledges are a thing at your organization.

Alex Wong

@Laura Neidhold
what you have will work out well for the “2 out of X” situation, as your query is looking for having gift in a year AND having another gift after that year. Won't work well if 3 or more out of X.

While this method won't work if you need the query itself to show the exact constituent that meets the criteria, it is more flexible and easily to handle:

constituent query:

critera: “summary of gift amount greater than 0” with sub criteria of gift date between 1/1/2020 and 1/1/2024. (this is to avoid dup record rows if criteria using “gift date” that is a 1-to-many relationship to constituent)

output: all info you want and “summary of gift amount” with sub criteria for each year range

in excel, there is a few ways you can determine “X out of Y” giving columns being greater than 0.