Future date greater than currentdate 1

[LLowrance]

CR 8.5
SQL Database

Pardon the title, I'm sure sure what to call this question.
I have a history table that tracks when a claimant visits the doctor. So there can be mulitple records per claimant. The visits can be in the past or in the future. It looks something like this:

Visit_key Next_Visit_Date
1 10/1/05
2 11/1/05
3 12/28/05
4 1/17/06

I'm trying to find the next date in the future. So if today is 12/2/05, I need 12/28/05 to appear in the report.

I've accomplished this with a subreport but selecting all dates greater than currentdate, then grouping by the claimant and finding the minimum date from there. It's not the way I would like to go as it makes the report run slow.

Any suggestions of how I can obtain the correct date without a subreport?

Thanks,
L

 

[synapsevampire]

The same criteria would be correct in the main report.

Assuming that you mean that the above data is for the main report rows returned, then you could create a View on SQL Server somehting like:

select claimant, minimum(nextvisitdate) from table
where nextvisitdate > getdate()
group by claimant

To do this solely in one crystal report you might use:

Group by the claimant, and in the Report->Edit Record Selection->Group use:

{table.nextvisitdate} = minimum({table.nextvisitdate},{table.claimant})

In the Report->Edit Selection Formula->Record use:

{table.nextvisitdate} > currentdate

-k