I have some very specific constraints that I need to use to select transactions based on gift date and source.
I am looking at gifts received based on a certain "Appeal". The relevant information is the donor, the gift date, amount, and appeal. These are my tables of data that are relevant:
CnIDKEY GfLINK
1 1
2 2
3 3
4 4
Gift Table:
GfIDKEY GfDate GfAmount GfLINK AppealLINK
1 9/10/07 $20 1 1
2 1/12/08 $55 1 2
3 5/6/09 $80 1 3
4 4/7/10 $35 1 4
Appeal Table:
AppealIDKEY AppealID AppealLINK
1 U 1
2 C 2
3 G 3
4 C 4
Here then would be the data:
Donor GiftID GiftDate GfAmount AppealID
1 1 9/10/07 $20 U
1 2 1/12/08 $55 C
1 3 5/6/09 $80 G
1 4 4/7/10 $35 C
Here are the ones I want to include:
(1) All gifts with "C" appeal (Row 2 and 4 included no matter what)
(2) IF the donor's most recent gift before their first "C" gift was 2 years or more ago, include all subsequent gifts to the first "C" gift (in the table above, then, that WOULD include gift 3, and gift 4 by the condition (1)).
(3) IF the donor's most recent gift before their first "C" gift was less than 2 years ago, exclude all subsequent gifts to the first "C" gift unless the appeal is "C". In the table above, then, that WOULD NOT include gift 3, but gift 4 would still be included.
This may seem terribly complicated and I'm not sure if it's really possible in Crystal, or some kind of query?
Thanks much for any insight.
IP Logged
