Grouping on minimum date in a datetime field

[mdj3366]

I have a report that i'm using to find the oldest date entry in the database for a datetime field. For example, when someone is loaded into the database for the first time, the employee.entry field is populated with the date and time of the entry. Each time the employee’s data is updated, another date time record is created for that person in the employee.entry field. I need to group by month and year, on the oldest date, and be able to sum the number of employees entered within that month and year. I am using CR 9, but have access to 11, as well. Any help would be appreciated.

 

[lbass]

You can create a SQL expression {%mindate} like this:

(
select min(`entry`)
from employee A
where A.`employeeID` = employee.`employeeID`
)

The punctuation would be specific to your datasource. Correct the name of the employee field.

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

{employee.entry} = {%mindate}

You can also insert a group on {%mindate}.

-LB

 

[mdj3366]

Hi LB,

I created the following SQL expression as suggested:

(
select min("Employee"."entry_datetime")
from employee
where "Employee"."employee_id"="Employee"."employee_id"
)

It is returning the oldest payment entry record in the entire database, not the oldest record for each individual.

 

[lbass]

You didn't set it up correctly. If these are your exact field names, then use the following exactly:

(
select min("entry_datetime")
from "Employee" A
where A."employeeID" = "Employee"."employeeID"
)

The alias table "A" is creating a faux group in the where clause. Do not reference the table within the summary.

-LB