PS Query: How do you add an additional criteria within an effective dated subquery
This returns the EEs last transfer/promotion record. I want to build this query within Query Manager:
select J.EMPLID, J.DEPTID, J.EFFDT, E.NAME
FROM PS_JOB J, PS_EMPLOYEES E
where J.EMPLID = E.EMPLID
and J.EMPL_RCD = 0
and E.EMPL_RCD = 0
and J.EFFDT = (select MAX(J1.EFFDT) from PS_JOB J1
where J1.EMPLID = J.EMPLID
and J1.EMPL_RCD = J.EMPL_RCD
and J1.ACTION in ('XFR', 'PRO')
and J1.EFFDT <= GETDATE())
My question is simple but I've been hammering this thing for hours. How do you add the clause "and J1.ACTION