Select statement involving a minimum date

[technonurse]

I am using Crystal Reports 9.

I created a formula to show me the minimum date but cannot select it in my query. It says that this must be evaluated later. How do I create a report where the resulting "n" only reflects the MINIMUM date within a date range?

Technonurse-Spokane

 

[fisheromacse]

I have not used Crystal Reports 9, so i apologize if this is incorrect for that version...it works in version xi.
I also am uncertain what you mean by a date range. Are your users entering parameters for start and end dates?

The method i am outlining creates a SQL Expression to find the minimum date and then the select statement to limit the results in the report.

Replace my table & field names with those of yours.


1st create a SQL expression {%MinDate}:
(
select min("DateField")
from TABLENAME
)


you could add a where clause if you like, for example:
(
select min("DateField")
from TABLENAME
where "TABLENAME"."FieldMatch" = "TABLENAME2"."FieldMatch2"
)


2. Then in the select expert do the following:
{TABLENAME.DateField}={%MinDate}

 

[technonurse]

The date range is in my select statement; not in the parameters. Where in the report can I insert this SQL statement? I am unfamiliar with that?

Technonurse-Spokane

 

[lbass]

Are you using a daterange parameter? Or just hard coding a date range? What do you mean by minimum date then? Please explain more about what you are trying to do. Fisheromacse was trying to show you how to select the minimum date from a set of dates in the report data.

-LB

 

[fisheromacse]

In crystal reports, in the Field Explorer, right click on 'SQL Expression Fields' and choose New...


So, do i have this right? for your date range, in the select statement you have something like this:
(
TABLENAME.DateField > {@StartDate}
AND
TABLENAME.DateField < {@EndDate}
)