Complicated Query Building

Aaron Rothberg:

Hi Joelle-


I tried a Constituent query with the following options:


Criteria:


MEMBERSHIP -> HISTORY -> EXPIRES ON -> <today's date one year ago>


AND


TOTAL AMOUNT OF GIFTS Greater Than 500

GIFT DATE Greater Than <day before today's date one year ago>

GIFT TYPE Is One Of <everything except Pledges and Write Offs>


Output:


NAME

HISTORY EXPIRES ON

TOTAL AMOUNT OF GIFTS



I can already see some problems. For example, I have one constituent whose membership expires on 4/12/17. That means my summary gift dates are not valid for that one constituent and I would have to adjust my summary dates to start at 4/12/17. You'll run into this problem with each constituent that has a different Membership Expires On value not to mention my example will not find constituents with a Membership Expires on earlier than 4/12/17 who might have given $500 in the following 365-day period.


I think you'll need to do this in Excel exporting all Members new and old which will allow you to leverage Today(), EDATE(), and other date functions in combination with the History Expires On date. I have no idea how you would go back to get giving summary information since the date range will depend upon the History Expires On date. You might have to export all gifts for all Members on another spreadsheet and again, leverage the History Expires On date in a fomula to sum gifts from another wooksheet in your workbook. I can't see a quick an easy way to do this. Good luck!

Cathleen Mai:

Do you really have to run this daily?

For a monthly run I could think of this:

Run the Donor Category report (under Reports/ Analytical Reports) with a level set to $500 or more (Levels need first to be set up in Config - Donor Categories) for a time range of 1 year from the run date. Create a constituent output query (static)

The next month, do the same, just change the date. Compare the 2 output queries and see who is new (by merging)

Once they are members, to track if they lapse is something the membership module should be able to do, but I don't have it, so I don't know.

I'm using recurring gifts to track this for our organisation. Lot of work for a free membership ;-)


 

Steven Cinquegrana:

Hi Joelle,


A couple of "structural" options for you here:


1. Break the problem into component queries and use RE merge queries to combine your results (with AND, OR, SUB, XOR). This way you can more clearly see how each part of your query is behaving as you change criteria (WHERE parameters).


2. My own personal favorite approach: Use direct SQL on the back-end. Even if you absolutely must have the query in RE - for reporting, etc - you can run the SQL in a much more flexible manner and check your definitions, execution plans, etc and then migrate the result back into an RE query (or queries)*. Far and away, this is my preferred approach to complicated stuff like this. (And you could even go a step further and adopt Reporting Services as your reporting tool, but that's another story.)


Generally speaking, the complication with these things is actually the defiition of the problem (stating the obvious, I guess). If you're finding that there are exceptions to your original definition, then these have to find their way into the definition somehow and breaking things up into components really helps with that in my experience.


Cheers,

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions


* Don't forget to use View/SQL (Ctrl+Q) if you have the - optional and usually free - RODBA module. It's a god-send for this kind of thing - even for us atheists!