Print Page | Close Window

Difficult Select Formula

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=16092
Printed Date: 22 May 2024 at 4:24pm


Topic: Difficult Select Formula
Posted By: JFinzel
Subject: Difficult Select Formula
Date Posted: 04 Apr 2012 at 9:28am
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.


Replies:
Posted By: lockwelle
Date Posted: 05 Apr 2012 at 5:26am
I think, given that you need to look at more than just the immediate record to determine whether or not to include in it in the recordset, the easiest course would be to use a stored procedure to do your work, since it can look at groups of records, unlike CR which looks at the current record only.
 
You might be able to accomplish some of this in the record filter based on delimited strings that you build, but I know that I would much rather use a stored proc for the flexiblity allowed.
 
HTH

Posted By: JFinzel
Date Posted: 05 Apr 2012 at 10:23am
Thank you, lockwelle.  I am a newbie when it comes to SQL as the database I use does most of the work for me, but I was thinking that working more inside the database with querying before going to CR would probably give me more flexibility.
 
Thanks for taking the time to understand this.


Print Page | Close Window