Query to exclude one instance of a multiple attribute - help?

David Lacey

Hi. I am David Lacey, of Medecins Sans Frontieres in Sydney. We have an attribute called Reinstate Appeal, which tells us why a recurring gift has been reinstated. As gifts can be reinstated multiple times, this is a repeating attribute. We want to send a letter to tell people that they have been reactivated UNLESS the appeal code is MSF, which indicates it has been reinstated for an internal administrative reason. Our query has as one of its criteria 'Reinstate Appeal does not equal MSF', the intention being to find a reactivation on the gift that was a 'real' one. But of course once there has been an MSF Reinstate Appeal, then the gift is never found again. I feel that thare should be an easy way to do this - but I cannot create a query that finds these gifts. Part of the problem seems to be that I can't find a way of tieing the description with its date when there are multiple instances of the attribute. Any clues/suggestions/ideas welcome.

Steven Cinquegrana 2

Hi David,


I can't go into full details due to the lateness of the hour here, but I would approach this in one of two ways:

1. Direct SQL

This is only an option if you are not hosted by Blackbaud and therefore have direct access to your back-end database (via SQL Server Management Studio, etc). I haven't provided further details because if you can go down this route you probably already know how to approach things.

2. RE Merge Queries

Due to the nature of SQL queries - on which RE queries are based - the relationship between "things" can sometimes not be as straight-forward as they might intuitively seem. (Apologies of you are SQL-literate but I'm writing this in part for other people searching the forums who may not be.) RE provides four ways to post-process queries to take care of things like combining result sets (OR), subtracting result sets from one another (SUB), selecting common records in result sets (AND), etc. (Merge Queries also allow you to de-duplicate a single result set in cases where RE Query's own Supress duplicate rows doesn’t.)


In your case, the primary query might contain all records meeting the Reinstate Appeal criterion, while a secondary query might contain those records that meet both the Reinstate Appeal as well as the Appeal Code Does Not Equal MSF criteria. Merging these two queries using SUB will remove the secondary query's result set from the first's, hopefully leaving you with the data you are after. (I may have gotten the nuance of your requirement wrong, but this is the general approach.)


(In my opinion, doing this in direct SQL is much clearer and easier, but to each her/his own.)


Cheers,

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

 

David Lacey

Steven Cinquegrana:

Hi David,


I can't go into full details due to the lateness of the hour here, but I would approach this in one of two ways:

1. Direct SQL

This is only an option if you are not hosted by Blackbaud and therefore have direct access to your back-end database (via SQL Server Management Studio, etc). I haven't provided further details because if you can go down this route you probably already know how to approach things.

2. RE Merge Queries

Due to the nature of SQL queries - on which RE queries are based - the relationship between "things" can sometimes not be as straight-forward as they might intuitively seem. (Apologies of you are SQL-literate but I'm writing this in part for other people searching the forums who may not be.) RE provides four ways to post-process queries to take care of things like combining result sets (OR), subtracting result sets from one another (SUB), selecting common records in result sets (AND), etc. (Merge Queries also allow you to de-duplicate a single result set in cases where RE Query's own Supress duplicate rows doesn’t.)


In your case, the primary query might contain all records meeting the Reinstate Appeal criterion, while a secondary query might contain those records that meet both the Reinstate Appeal as well as the Appeal Code Does Not Equal MSF criteria. Merging these two queries using SUB will remove the secondary query's result set from the first's, hopefully leaving you with the data you are after. (I may have gotten the nuance of your requirement wrong, but this is the general approach.)


(In my opinion, doing this in direct SQL is much clearer and easier, but to each her/his own.)


Cheers,

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

 

Thanks Steve - I will explore these options - looks likely this will solve my problem

David