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
The issues is that a single contribution is not going to be in all Fiscal years you have listed.
We have created custom list elements that will make this easier. However this would not be easy to explain here.
You can try to contact your IT staff to see if they can puting this in a manualy edited query that produces the results you are trying to produce.
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!
Hi Ruthie,
To rephrase Tom's post a bit, the query as it gets written in the criteria set is trying to make two date ranges true for a single line item, which is not possible. (I think I've got that right)To get the results that you are looking for you would need to either manually edit the query, or as Tom states build some custom t_keyword elements.As a workaround, if you can use Campaigns to get your results, you should be able to create a separate entry in the set for each campaign to be considered with the operator of "has" which will should look for everyone that has given to all of those campaigns.
Hope that helps.
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
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!
Sandra,
As far as I can tell, your query uses Contribution date (not fiscal year date) and does not find the folks who have a donation in each of the fiscal years. Which is what I think that Ruthie is looking for.
Here is an alternate that may help.
Select z.customer_nofrom (Select a.customer_no,max(case when f.fyear = 2006 then 1 else 0 end) +max(case when f.fyear = 2007 then 1 else 0 end) +max(case when f.fyear = 2008 then 1 else 0 end) +max(case when f.fyear = 2009 then 1 else 0 end) +max(case when f.fyear = 2010 then 1 else 0 end) +max(case when f.fyear = 2011 then 1 else 0 end) as 'year_count'From T_CUSTOMER a (NOLOCK)join dbo.VS_CONTRIBUTION e (NOLOCK) ON a.customer_no = e.customer_noJOIN dbo.VS_CAMPAIGN f (NOLOCK) ON e.campaign_no = f.campaign_noWhere IsNull(a.inactive, 1) =1 AND f.fyear between 2006 and 2011group by a.customer_no) as zwhere z.year_count = 6
Hope this helps.
--
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,