Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

consecutive years giving

Status
Not open for further replies.

CrisE

Technical User
Feb 6, 2013
6
US
I need to pull id number, name etc for each donor that has given 20 consecutive years. That is, any 20 consecutive years not just the last 20. Data will be pulled from one table. I am using Crystal Report 11. Any help would be appreciated.

TIA
 
I do not have crystal in front of me, so apologize in advance for any errors.

group by doner ID.
sort within group by date.
use a formula to decide if the current record's date is 1 year more than the previous record's date and act accordingly.

//{@datecheck}
numbervar dc;
IF onfirstrecord=true then dc := 1 else
IF Previous ({table.DonerID}) <> ({table.DonerID}) THEN dc := 1 ELSE
IF Year({table.data}) = year(previous({table.date}))+1 then dc := dc+1 else dc := dc;


If it is possible that a doner may give more than once a year, the formula would need a little modification.
 
It is possible (very likely) they give more than one gift in a year.

Thanks
 
i think the changes below should work.


//{@datecheck}
numbervar dc;
IF onfirstrecord=true then dc := 1 else
IF Previous ({table.DonerID}) <> ({table.DonerID}) THEN dc := 1 ELSE
[highlight #CC0000]IF Year({table.data}) = Year(previous({table.data})) then dc := dc ELSE[/highlight]
IF Year({table.data}) = year(previous({table.date}))+1 then dc := dc+1 else dc := dc;


 
I think I got it but now am getting an error saying "This function cannot be used because it must be evaluated later" that is referring to onfirstrecord.
 
Oracle and no I am not allowed to create views.

CrisE
 
Had to move on to other things but am now back on this project. Any help would be appreciated.
 
Try removing else and treat each test separately

numbervar dc;
IF onfirstrecord then dc := 1;
IF not onfirstrecord and Previous ({table.DonerID}) <> ({table.DonerID}) THEN dc := 1;
//IF Year({table.data}) = Year(previous({table.data})) then dc := dc ELSE
// do not need this as dc will remain unchanged unless any conditions met
IF Year({table.data}) = year(previous({table.date}))+1 then dc := dc+1 else dc := dc;

Ian
 
Thanks Ian, but still getting the same error.
 

Let dct be a range of distinct CONTIGUOUS values

The AVERAGE of dct is MIN(dct) + (MAX(dct) - MIN(dct)) / 2

The COUNT of dct is MAX(dct) - MIN(dct) + 1

The SUM of dct is AVERAGE * COUNT

If the SUM equals that sum of the range of distinct ACTUAL values in your table, then you have contiguous years.

How you implement that in CR, is yet another question.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top