Hello LawsonTalk folks -

So I have a report that is suppose to display the Most course completion Date for an employee

In the Detail Section:

The courses are respresented by my formula:

select {PATRNHIST.COURSE}
case "2C00000001":
ToText({PATRNHIST.DATE_COMPLETED}, "M/dd/yyyy")
Case "2C00000002":
ToText({PATRNHIST.DATE_COMPLETED}, "M/dd/yyyy")
case"2C00000003":
ToText({PATRNHIST.DATE_COMPLETED}, "M/dd/yyyy")
case"1C00000001":
ToText({PATRNHIST.DATE_COMPLETED}, "M/dd/yyyy")
default:
""
Those 4 courses equal a Mandatory BLS class - if they take anyone of those I need the latest date.

So, I took the detail and did an NthLargest=1 and put it on the GH1 (Employee) But, it looks like people with multiple courses taken - it is not grabbing the most recent??

Here's the SQL Query

SELECT "PATRNHIST"."DATE_COMPLETED", "PATRNHIST"."COURSE", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."FIRST_NAME", "EMPLOYEE"."MIDDLE_INIT", "EMPLOYEE"."DEPARTMENT", "DEPTCODE"."R_NAME", "EMPLOYEE"."EMP_STATUS"
FROM "LAWSON"."PATRNHIST" "PATRNHIST", "LAWSON"."EMPLOYEE" "EMPLOYEE", "LAWSON"."DEPTCODE" "DEPTCODE"
WHERE ("PATRNHIST"."EMPLOYEE"="EMPLOYEE"."EMPLOYEE") AND ("EMPLOYEE"."DEPARTMENT"="DEPTCODE"."DEPARTMENT") AND NOT ("EMPLOYEE"."EMP_STATUS"='CB' OR "EMPLOYEE"."EMP_STATUS"='RT' OR "EMPLOYEE"."EMP_STATUS"='XF' OR "EMPLOYEE"."EMP_STATUS"='XI' OR "EMPLOYEE"."EMP_STATUS"='XP' OR "EMPLOYEE"."EMP_STATUS"='XR' OR "EMPLOYEE"."EMP_STATUS"='XU' OR "EMPLOYEE"."EMP_STATUS"='XV' OR "EMPLOYEE"."EMP_STATUS"='XZ' OR "EMPLOYEE"."EMP_STATUS"='ZA' OR "EMPLOYEE"."EMP_STATUS"='ZB' OR "EMPLOYEE"."EMP_STATUS"='ZC' OR "EMPLOYEE"."EMP_STATUS"='ZO' OR "EMPLOYEE"."EMP_STATUS"='ZP' OR "EMPLOYEE"."EMP_STATUS"='ZS' OR "EMPLOYEE"."EMP_STATUS"='ZV' OR "EMPLOYEE"."EMP_STATUS"='ZX') AND (("PATRNHIST"."COURSE"='1C00000001' OR "PATRNHIST"."COURSE"='2C00000001' OR "PATRNHIST"."COURSE"='2C00000002' OR "PATRNHIST"."COURSE"='2C00000003') AND "EMPLOYEE"."DEPARTMENT"='6220' AND ("PATRNHIST"."DATE_COMPLETED">=TO_DATE ('19-03-2005 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "PATRNHIST"."DATE_COMPLETED"<TO_DATE ('19-03-2007 00:00:01', 'DD-MM-YYYY HH24:MI:SS')) OR ("PATRNHIST"."COURSE"='AM00000003' OR "PATRNHIST"."COURSE"='AM00000005' OR "PATRNHIST"."COURSE"='AM00000007' OR "PATRNHIST"."COURSE"='AM00000009' OR "PATRNHIST"."COURSE"='AM00000011' OR "PATRNHIST"."COURSE"='AM00000013' OR "PATRNHIST"."COURSE"='AM00000014') AND "EMPLOYEE"."DEPARTMENT"='6220' AND ("PATRNHIST"."DATE_COMPLETED">=TO_DATE ('19-03-2006 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "PATRNHIST"."DATE_COMPLETED"<TO_DATE ('19-03-2007 00:00:01', 'DD-MM-YYYY HH24:MI:SS')))