Query Creation

Romulus Patterson

Afternoon,

I am tring to get a list of constituents that have at least X of the last Y years. They do not have to be consecutive. Was wondering if anyone else had to pull anything like this. Any help will be greatly appreciated.


Thank you

Cathleen Mai

Here is a string of a discussion that might help:

Donors who have given a set number of years, but not necessarily consecutive years

https://community.blackbaud.com/forums/viewtopic/159/30270?post_id=110676#p110676

Steven Cinquegrana 2

Hi Romulus,


Can you clarify what you mean by, "... constituents that have at least X of the last Y years"? Do you mean, say, gifts for those years?


Cheers,

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

 

Romulus Patterson

Hi,

Yes, for example I want to find out how many
people have giving 10 out of 15 years. They do not have to be
consecutive.

 

Romulus
Patterson Jr

Associate
Business System Analyst

WellSpan
Philanthropy

Gettysburg Hospital Foundation |
York Health Foundation 

Ephrata Community Health Foundation
| Good Samaritan Hospital Foundation  |
Philhaven

50 N.
Duke Street, York, PA 17401

Phone:
717-812-3516 | Fax:717-848-5555

rpatterson2@wellspan.org

 

---

The information contained in this communication, including all
attachments, is legally protected information, confidential or
proprietary information, or a trade secret intended solely for the
use of the intended recipient or otherwise exempt from disclosure
under applicable state and federal law, including, but not limited
to, privacy standards imposed pursuant to the federal Health
Insurance Portability and Accountability Act of 1996 (“HIPAA”). The
information may also be subject to legal privilege. If you are not
the intended recipient, you are hereby notified that any use,
disclosure, dissemination, distribution, forwarding, or copying of
this communication is strictly prohibited. If you have received
this communication in error, please notify the sender by reply Fax
or e-mail stating the communication was "received in error" and
delete or destroy all copies of this communication, including all
attachments.


Message string: KqxfH=TU

Steven Cinquegrana 2

The link Cathleen provided is a virtually identical question (albeit for an Export), so should provide you with a good starting point.


Another alternative - and the approach I would take, though possibly not the answer you're looking for - is to use direct SQL (via, say, SQL Server Management Studio). In this situation, direct SQL makes this a cinch as opposed to a lot of convolution using RE Queries, etc. Also, you can easily define your "years" - calendar, financial (if different), even some arbitrary year such as May 1 to April 30.


Some things are just far harder using RE queries, exports, etc than hitting the backend data directly. (IMHO.)


Cheers, Steve

 

Romulus Patterson

Thanks so much! I appreciate the help.

 

 

 

Romulus
Patterson Jr

Associate
Business System Analyst

WellSpan
Philanthropy

Gettysburg Hospital Foundation |
York Health Foundation 

Ephrata Community Health Foundation
| Good Samaritan Hospital Foundation  |
Philhaven

50 N.
Duke Street, York, PA 17401

Phone:
717-812-3516 | Fax:717-848-5555

rpatterson2@wellspan.org

 

---

The information contained in this communication, including all
attachments, is legally protected information, confidential or
proprietary information, or a trade secret intended solely for the
use of the intended recipient or otherwise exempt from disclosure
under applicable state and federal law, including, but not limited
to, privacy standards imposed pursuant to the federal Health
Insurance Portability and Accountability Act of 1996 (“HIPAA”). The
information may also be subject to legal privilege. If you are not
the intended recipient, you are hereby notified that any use,
disclosure, dissemination, distribution, forwarding, or copying of
this communication is strictly prohibited. If you have received
this communication in error, please notify the sender by reply Fax
or e-mail stating the communication was "received in error" and
delete or destroy all copies of this communication, including all
attachments.


Message string: KqxfH=TU

Aaron Rothberg

Check out John Cioni's profile. He built a pivot report for this exact purpose and is willing to share it with others. I've used it and it met my needs.