My query is below. I need to evaulate each row returned (and provide a "label" - shown with an astrick) based on the values in the 3 date columns and I'm stumped as to how to do it.

Here are my conditions and labels:
Action_Dt = Effdt AND Action_Dt < cutoff_dt- OK*
Action_Dt = Effdt AND Action_Dt > cutoff_dt - Noted*
Action_Dt > cutoff_dt AND Effdt < cutoff_dt - Late*
Action_Dt > cutoff_dt AND Effdt > cutoff_dt - Noted*
Action_Dt < cutoff_dt AND Effdt < cutoff_dt - OK*
Action_Dt < cutoff_dt AND Effdt > cutoff_dt - Data Entry Issue*

select P.NAME, C.CUTOFF_DT, J.ACTION_DT, J.EFFDT, Q.NAME
from PS_PERSONAL_DATA P,
ps_job j,
SYSADM.PS_DEPT_TBL D,
PS_PERSONAL_DATA Q,
SYSADM.PS_PAY_CALENDAR C
WHERE J.EMPLID = P.EMPLID
AND D.DEPTID = J.DEPTID
AND D.PM = Q.EMPLID(+)
AND J.PAYGROUP = C.PAYGROUP
AND J.EFFDT >= C.PAY_BEGIN_DT
AND J.EFFDT <= C.PAY_END_DT
and D.effdt = (select max(D1.effdt) from SYSADM.PS_DEPT_TBL D1
where D.DEPTID = D1.DEPTID)

Can it be done?

can you kindly post your table structure for reference of our experts in the forum.

Do you mean a describe of each of the tables in my query?

Describes attached. Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.