Compare earning code report

[christimess]

I am trying to create a report where you select a check date and earning code and it will compare that information to the last check date and if that earning code is not there it will list that employee on the report.
We thought of doing this with a derived field. We created the following derived field:
(SELECT 'Y' FROM PS_AL_CHK_HRS_ERN B
WHERE B.EMPLID = "CHK"."EMPLID"
AND B.EMPL_RCD_NBR = "CHK"."EMPL_RCD_NBR"
AND B.ERNCD = "CHK"."ERNCD"
AND B.CHECK_DT = (
SELECT MAX(C.CHECK_DT)
FROM PS_AL_CHK_DATA C
WHERE C.EMPLID = "CHK"."EMPLID"
AND C.EMPL_RCD_NBR = "CHK"."EMPL_RCD_NBR"
AND C.CHECK_DT < "CHK"."CHECK_DT"))
However it is not working properly and is listing employees that should not be on the report.
The problem may be that when a sub-select statement is done within the ‘Select’ clause in the SQL Statement, is that it may cause issues when it returns more than one row.

Does anyone have any suggestions on how I can get this derived field to work?
Thanks!