Recently a customer asked me how to determine who has made their final pledge payment within the last 37 days (the choice of 37 was an arbitrary one) AND has no other open pledges? My first thought was, "Wow! What a great question! This fundraiser understands the importance of renewing a donor relationship that is freshly expired." My next thought, was "Hmmm..... How do I find them?"
After a few false starts I believe I have the answer. It has not withstood the test of time, but I'm feeling good about it.
The query text below looks complicated to me too. The ONLY part you need to focus on is the number 37. You can change that to any number of days you wish. If you want to see who made a final pledge payment within the last 60 days, then change the 37 to 60.
You would copy the following text into the SQL tab of your Donor/Voter/Contact/Member list.
((EXISTS (SELECT CL044006 FROM TB044002 LEFT OUTER JOIN TB044001 ON CL043998=CL044007
WHERE ((TB044002.CL044008=TB008485.CL005500) AND
((( ISNULL (( SELECT SUM ( CL044056 ) FROM TB044002 WHERE ( CL044008 = TB008485.CL005500 )) ,0 ))
- ( ISNULL (( SELECT SUM ( CL045932 ) FROM TB045928 WHERE ( CL045938 = TB008485.CL005500 )) ,0 ))
- ( ISNULL (( ISNULL (( SELECT SUM ( CL048923 ) FROM TB044002 WHERE ( CL044008 = TB008485.CL005500 )) ,0 )) ,0.0 )))= 0 ))
and TB044002.cl044170 > getdate () -37 )
It looks like this when pasted (click the image for a larger view in your full screen browser):
Your results will vary. Save the query as a Favorite and you will not need to repeat these steps.