No Future Dates

[AyJayEL]

I need to find out people whose term of service is ending within a range parameter of dates who don't have a future service set up. They may have several or only one previous service.

I am using Oracle and Crystal 8.

for example I don't want to see the following

ID Fred Bloggs Start date 1/1/2004 End date 16/6/2006
ID Fred Bloggs Start Date 20/6/2006 End date sometime in the future.

I have grouped by Company and Person ID.

Learn something new every day *:->*
AyJayEl

 

[lbass]

I think your record selection formula should look something like:

{table.enddate} >= minimum({?daterange})

Then go to report->selection formula->GROUP and enter:

maximum({table.enddate},{table.personID}) >
maximum({table.startdate},{table.personID}) and
maximum({table.enddate},{table.personID}) <= maximum({?daterange})

-LB

 

[AyJayEL]

Thank you LB. I've added that into my report but I get the message The summary/running total field could not be created.

Learn something new every day *:->*
AyJayEl

 

[lbass]

The second formula has to be in the GROUP selection formula area, not the record, and you must have a group inserted on PersonID.

-LB

 

[AyJayEL]

Yes I went into the group selection formula area (Report|Edit Selection Criteria|Group) and added the following;-
maximum({GOV_LINK.END_DATE},{PEOPLE.PERSON_ID}) >
maximum({GOV_LINK.START_DATE},{PEOPLE.PERSON_ID}) and
maximum({GOV_LINK.END_DATE},{PEOPLE.PERSON_ID}) <= maximum({?TermEnds})

Then I went into the record selection area (Report|Edit Selection Criteria|Record) and added the following;-
{GOV_LINK.END_DATE} >= minimum({?TermEnds}) and
{GOV_LINK.GOV_TYPE} = {?GovernorType}

I have a group on People.Person_ID (second group) after a group on Company (First Group)


Learn something new every day *:->*
AyJayEl

 

[lbass]

I was assuming that you were using a range parameter. How do you have {?Term ends} set up? Otherwise nothing appears incorrect to me.

-LB

 

[AyJayEL]

Yes, {?Termends} is a range parameter.

Learn something new every day *:->*
AyJayEl

 

[lbass]

I just recreated this solution with a mock report and it worked perfectly with no error messages, so there is something you are doing or not doing that is not apparent in the thread.

-LB