Maximum Date formula 1

[Jewel142]

Hi -

I am trying to write a report that shows the last date an employee was paid. We have weekly paydata from 1/1/2005 thru present so there is a lot check detail.

I've tried using the Maximum({PRCHECK.CHECK_DATE}) function but that didn't work. It just pulled the last date in the series and not the last date the employee was paid.

Employees are grouped by employee number. Is there any way to pull just the last check date without all the other detail. If I use the greater than or equal to function, I may miss someone that has a check date prior to the critera date.

Thanks!

Jewel142

 

[lbass]

If you want to use group selection, the formula should be:

{PRCHECK.CHECK_DATE} = Maximum({PRCHECK.CHECK_DATE},{table.employeenumber})

...assuming you have a group on employeenumber.

A more efficient way would be to create a SQL expression {%maxdate} which would pull the most recent check:

(
SELECT (`CHECK_DATE`)
FROM PRCHECK A
WHERE A.`EMPLOYEE_NO` = PRCHECK.`EMPLOYEE_NO`
)

The punctuation depends upon your datasource.

Then in report->selection criteris->RECORD, enter:

{PRCHECK.CHECK_DATE} = {%maxdate}

-LB

 

[Jewel142]

I do have a group on the EE number and it worked. Thank you so much!!!