Minimum date from multiple records 1

[Nick366]

The database table I am looking at has legacy data. I am looking to get the minimum start date, but the rest of the data from the current record. Here is an example:

[tt]
Table: BOOKS
Fields: Name Description Release_Date Expired_Date
ABCook Cooking 12/30/2003 1/20/2004
ABCook Cooking Help 1/21/2004 1/25/2004
ABCook Help: Cooking 1/26/2004 {null}
[/tt]

The current output is: ABCook - Help: Cooking (1/26/04)
The desired output is: ABCook - Help: Cooking (12/30/03)

I am using Crystal Reports 8

 

[synapsevampire]

Use the minimum function, as in:

minimum({table.release_date})

If you're grouped by something and want the min for that grouping, use:

minimum({table.release_date},{table.mygroupfield})

-k

 

[lbass]

First group on whatever is clustering the books together, e.g., {books.booktype}. Then create a formula:

minimum({books.releasedate}, {books.booktype})

Place this in the detail section. Then go to report->edit selection formula->GROUP and enter:

{books.releasedate} = maximum({books.releasedate},{books.booktype})

This should return the most recent record per booktype group, but with the minimum release date as a field within the record.

To add the parens around the date, you would use a formula like:

"("+totext(minimum({books.releasedate}, {books.booktype}),"MM/dd/yyyy&quot+&quot"

-LB

 

[Nick366]

My selection formula is filtering for records with null values in the Expired_Date column. I think that is causing it to continue to display the 1/26/04. Is there anyway to have this pull the minimum from all records, grouped by BookName?

 

[Nick366]

Got it!!! Thanks guys!