consecutive years giving

[CrisE]

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

 

[fisheromacse]

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.

 

[CrisE]

It is possible (very likely) they give more than one gift in a year.

Thanks

 

[fisheromacse]

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;

 

[CrisE]

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.

 

[IdoMillet]

What DBMS? Are you allowed to create VIEWS in that DBMS?

- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[CrisE]

Oracle and no I am not allowed to create views.

CrisE

 

[CrisE]

Had to move on to other things but am now back on this project. Any help would be appreciated.

 

[IanWaterman]

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

 

[CrisE]

Thanks Ian, but still getting the same error.

 

[SkipVought]

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]
Just traded in my old subtlety...
for a NUANCE![/sub]