Hello,
I haven't been able to find a forum posting for this issue: I'm working on an extraction where I need to find out what donors give frequently. I built a criteria set that used my different date ranges of fiscal years all together, the contribution date ranges for Fiscal Years 2006, 2007, 2008, 2009, 2010, & 2011, so that it would consider it an "AND," not an "OR" (per the instructions of the documentation for the Criteria for Lists and Extractions) between the different date ranges.
This results in zero constituents being pulled. I decided to take away some of the date ranges, since it may have been too restrictive--maybe there really isn't one person who gave all 6 years? I troubleshooted a bit on my own, taking away all the date ranges except one works fine, I end up with the list of constituents who gave during that fiscal year. I even tried just doing two consecutive years, 2010 and 2011, which I know have some overlapping donors...but again, nothing. When I pulled for just one year, for 2010, I once more had a nice tidy list of constituents.
Does anyone know how to do this? I don't even need the constituents for every fiscal year, just the frequent ones, something that could tell me the donors that gave 3 or 4 out of the last 5 years is great.
greatly appreciated,
Ruthie Hubka
Ruthie
The way the question is being asked to the database is that the date of one contribution has a contribution date in both fiscal years. Which is not possible, hence no customers.
For other List Criteria the way around this is to use HAS rather than IN, but for a date range using the BETWEEN operand you don’t have that luxury.
Do you have access to the Manual Edit button in List Manager? If so, you could use the script below. Copy it into the Manual Edit area and generate your list from this. Modifying the 2 date ranges of course to suit what you need for your Fiscal Years.
Select Distinct a.customer_no
From t_customer a (NOLOCK)
JOIN vs_contribution e (NOLOCK) ON a.customer_no = e.customer_no
Where IsNull(a.inactive, 1) = 1
AND e.cont_dt between '2010/07/01' AND '2011/06/30 23:59:59' and
EXISTS (select 1 from vs_contribution (NOLOCK) where a.customer_no = vs_contribution.customer_no and vs_contribution.cont_dt between '2009/07/01' AND '2010/06/30 23:59:59')
You may be wondering why this is different to what List Manager did? Here’s the code List Manager automagically creates when you add the criteria.
AND e.cont_dt between '2010/07/01' AND '2011/06/30 23:59:59'
AND e.cont_dt between '2009/07/01' AND '2010/06/30 23:59:59'
What makes the first one work is that it is looking for a contribution that has a contribution date in the first date range and then it goes off and asks if there is another contribution exists in the 2nd date range. That’s what the EXISTS clause does.
Hope that helps to get what you are after and explains why you got 0 results.
Cheers
Sandra
From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Ruthie HubkaSent: Wednesday, 15 June 2011 9:32 AMTo: Sandra AshbySubject: RE: [Tessitura Development Forum] Pulling a list of donors who give consecutive years
I know that one contribution won’t be in all the years, but shouldn’t it pull the constituent if they have a contribution in both of the fiscal years that I have listed? Two gifts for example, one in my FY2010 date range, and the other in my FY2011 date range, from the same constituent. So if my criteria set was for the contribution date in the first date range AND for the other fiscal year, shouldn’t it pull that constituent because they gave in both years?
Am I not understanding the query implications of the contribution date criteria? I’m just trying to figure out how it should work. Thanks!
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
--
Ruthie,
If each of your fiscal years has its own campaign, another approach would be to abandon the dates altogether and just work with the campaigns directly. So you would have one criteria for each campaign using the "has" operator.
The other route (since you are in an extraction) is to build a series of suppression segments which suppress patrons who did NOT give in a singe fiscal year. Then in the final (non-suppression) segment, you ask for all the current year donors. The only people left should be those who were not suppressed earlier in the extraction (i.e. those who had given in each year).
I tried campaigns, too, but not with that operator, I'll give that a shot.
Thank you everyone for the help, it shed some light on the issue for us.
Best,