SELECT DISTINCT A.EMPLID , A.NAME_DISPLAY FROM PS_NAMES A ,PS_JOB B ,PS_REG_REGION_TBL C WHERE B.EMPLID=A.EMPLID AND C.REG_REGION=B.REG_REGION AND A.NAME_TYPE = 'PRI' AND C.COUNTRY='GBR' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = A.NAME_TYPE AND B.EFFDT <= %CurrentDateIn ) AND B.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID=B.EMPLID AND A1.EMPL_RCD=B.EMPL_RCD AND A1.EFFDT<=%CurrentDateIn) AND B.EFFSEQ=( SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A2.EMPLID=B.EMPLID AND A2.EMPL_RCD=B.EMPL_RCD AND A2.EFFDT=B.EFFDT)
|