|
SELECT DISTINCT A.INSTITUTION , A.SSR_RI_EDU_GOV_NLD , C.LOCATION , C.EFFDT , CASE WHEN LENGTH(C.ESTABID) = 7 THEN C.ESTABID END , C.DESCR FROM %Table(SSR_RI_EPS_NLD) A JOIN %Table(CAMPUS_TBL) B ON A.INSTITUTION = B.INSTITUTION JOIN %Table(LOCATION_TBL) C ON B.LOCATION = C.LOCATION JOIN %Table(SSR_RI_OLWL_NLD) OLW ON A.INSTITUTION = OLW.INSTITUTION AND C.LOCATION = OLW.LOCATION JOIN %Table(SET_CNTRL_REC) D ON A.INSTITUTION = D.SETCNTRLVALUE AND D.RECNAME = 'LOCATION_TBL' AND D.SETID = C.SETID WHERE %EffdtCheck(CAMPUS_TBL B1, B, %CURRENTDATEIN) AND %EffdtCheck(SSR_RI_EPS_NLD A_ED,A,%CURRENTDATEIN) AND A.EFFDT = OLW.EFFDT AND C.EFFDT >= ( SELECT MIN(C1.EFFDT) FROM PS_LOCATION_TBL C1 WHERE C1.SETID = C.SETID AND C1.LOCATION = C.LOCATION AND C1.EFF_STATUS = 'A') AND C.EFFDT <= ( SELECT MAX(C_ED.EFFDT) FROM PS_LOCATION_TBL C_ED WHERE C.SETID = C_ED.SETID AND C.LOCATION = C_ED.LOCATION AND C_ED.effdt <= %CurrentDateIn) AND ( C.EFFDT NOT IN ( SELECT C_IED.EFFDT FROM PS_LOCATION_TBL C_IED WHERE C.SETID = C_IED.SETID AND C.LOCATION = C_IED.LOCATION AND C_IED.EFFDT = ( SELECT MAX(C_IED1.EFFDT) FROM PS_LOCATION_TBL C_IED1 WHERE C_IED1.SETID = C_IED.SETID AND C_IED1.LOCATION = C_IED.LOCATION AND C_IED1.EFFDT > ( SELECT MAX(C_EDX.EFFDT) FROM PS_LOCATION_TBL C_EDX WHERE C_EDX.SETID = C.SETID AND C_EDX.LOCATION = C.LOCATION AND C_EDX.EFF_STATUS = 'A' AND C_EDX.EFFDT <= %CurrentDateIn ) AND C_IED1.EFF_STATUS = 'I' AND C_IED1.EFFDT <= %CurrentDateIn) ) )
|
INSTITUTION