How to write a Max effe date < 2 years in Application Design PeopleSoft

Question

I need to pull the students data who is inactive for more than 2 continuous years.

Answer 1

Maybe something like:

SELECT EMPLID  
 , INSTITUTION  
 , ACAD_CAREER  
 , STDNT_CAR_NBR  
 , EFFDT
 , PROG_STATUS
  FROM %Table(ACAD_PROG) A 
 WHERE PROG_STATUS <> 'AC'
   AND %EffdtCheck(acad_prog A2, A, %currentdatein) 
   AND EFFSEQ = ( 
 SELECT MAX(EFFSEQ) 
  FROM %Table(ACAD_PROG) A3 
 WHERE A3.EMPLID=A.EMPLID 
   AND A3.ACAD_CAREER=A.ACAD_CAREER 
   AND A3.STDNT_CAR_NBR=A.STDNT_CAR_NBR 
   AND A3.EFFDT = A.EFFDT) 
   AND %DateDiff (A.EFFDT, %currentdatein) > 730

You might need to update the PROG_STATUS criteria to meet your needs, but the key is AND %DateDiff (A.EFFDT, %currentdatein) > 730

We compare the MAX(EFFDT) to today's date