SELECT DISTINCT A.EMPLID ,A.STDNT_CAR_NBR ,A.ACAD_PROG FROM PS_ACAD_PROG_WHIF A WHERE A.ADVIS_STATUS <> 'NINC' AND A.STDNT_CAR_NBR = ( SELECT MIN(D.STDNT_CAR_NBR) FROM PS_ACAD_PROG_WHIF D ) AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_ACAD_PROG_WHIF B WHERE B.EMPLID = A.EMPLID AND B.ACAD_PROG = A.ACAD_PROG AND B.STDNT_CAR_NBR = A.STDNT_CAR_NBR ) AND A.EFFSEQ = ( SELECT MAX(C.EFFSEQ) FROM PS_ACAD_PROG_WHIF C WHERE C.EMPLID = A.EMPLID AND C.ACAD_PROG = A.ACAD_PROG AND C.STDNT_CAR_NBR = A.STDNT_CAR_NBR AND C.EFFDT = A.EFFDT )
|