SELECT A.ACAD_CAREER, C.ACAD_PROG_PRIMARY, A.SEL_GROUP, CASE WHEN C.UNT_TAKEN_PRGRSS BETWEEN 12 AND 30 THEN 'Full' WHEN C.UNT_TAKEN_PRGRSS BETWEEN 0.01 AND 11.99 THEN 'Part' ELSE ' ' END, A.ITEM_TYPE, B.DESCR, B.KEYWORD1, count ( A.EMPLID), sum ( C.UNT_TAKEN_PRGRSS), SUM ( A.ITEM_AMT), TO_CHAR(SYSDATE,'YYYY-MM-DD'),B.SETID,B.ITEM_TYPE,TO_CHAR(B.EFFDT,'YYYY-MM-DD') FROM PS_ITEM_SF A, PS_ITEM_TYPE_TBL B, PS_STDNT_CAR_TERM C WHERE A.BUSINESS_UNIT = :1 AND A.ACCOUNT_TERM = :2 AND B.ITEM_TYPE = A.ITEM_TYPE AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_TYPE_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.ITEM_TYPE = B_ED.ITEM_TYPE AND B_ED.EFFDT <= SYSDATE) AND A.BUSINESS_UNIT = B.SETID AND A.ITEM_AMT <> 0 AND A.EMPLID = C.EMPLID (+) AND A.ACAD_CAREER = C.ACAD_CAREER (+) AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR (+) AND A.BUSINESS_UNIT = C.INSTITUTION (+) AND A.ACCOUNT_TERM = C.STRM (+) GROUP BY A.ACAD_CAREER, C.ACAD_PROG_PRIMARY, A.SEL_GROUP, CASE WHEN C.UNT_TAKEN_PRGRSS BETWEEN 12 AND 30 THEN 'Full' WHEN C.UNT_TAKEN_PRGRSS BETWEEN 0.01 AND 11.99 THEN 'Part' ELSE ' ' END, A.ITEM_TYPE, B.DESCR, B.KEYWORD1,B.SETID,B.ITEM_TYPE,TO_CHAR(B.EFFDT,'YYYY-MM-DD') ORDER BY 1, 2, 3, 4, 5 | |||||||||||||||||||||