Background:
Stephanie Govan already uses CU_BAR_SR_00045_1. She is requesting some additional data in the report. Based on the additional data, some of which is undergraduate or graduate specific, it might make sense to split out the query based on career.
Final Report Delivery with requirements:
CU_BAR_SR_00045_3 Prompts are below:
- Institution: Bar01
- Career: GRAD
- Term: pick any term
- Degree: (optional) ( pick any degree)
- AcadProg (optional) If you want to be more specific
- AcadPlan (optional) If you want to be more specific
Query Development:
Following table shows all necessary records that have been used to generate fields as requested by an user to create this report.
Records | Description of fields |
---|---|
ACAD_PROG | EMPLID, ADMIT TERM,ACAD_CAREER, ACAD_PROG, |
ACAD_PLAN | ACAD_PLAN,PLAN_SEQ, |
ADM_APPL_DATA | ADMIT_TYPE |
CU_BOI_VW | LAST_NAME, FIRST_NAME, MIDDLE_NAME, PHONE, EMAIL_ADDR |
STDNT_CAR_TERM | ACADEMIC LEVEL,UNT_TAKEN_PRGRSS,TOT_CUM,TOT_PASSED_GPA, CUM_GPA |
ACAD_PLAN_TBL | DESCR, DEGREE, SSR_NSC_CRD_LVL(DEG LEVEL) |
SERV_IND_DATA | SRVC_IND_CD for 'APS' have used it with reason code |
SERV_IND_DATA | SRVC_IND_CD for 'ACP' have used it with reason code |
ACAD_SUBPLAN | DESCR, DEGREE, SSR_NSC_CRD_LVL(DEG LEVEL) |
ACAD_PLAN | ACAD_PLAN |
ACAD_SUBPLAN | ACAD_SUB_PLAN |
STNDT_GRPS | Student Group (SG) |
Expressions:
Few 'Expressions' have been added to find 'Academic Level' have described the prompt as null and define the value of 'ACP' and 'APS'. Reference for Service Indicator: Service Indicators
decode ( F.ACAD_LEVEL_BOT, '11', 'Lower Freshman') || decode ( F.ACAD_LEVEL_BOT, '15', 'Upper Freshman')|| decode ( F.ACAD_LEVEL_BOT, '21', 'Lower Sophmore') || decode ( F.ACAD_LEVEL_BOT, '25', 'Upper Sophmore')|| decode ( F.ACAD_LEVEL_BOT, '31', 'Lower Junior') || decode ( F.ACAD_LEVEL_BOT, '35', 'Upper Junior') || decode ( F.ACAD_LEVEL_BOT, '41', 'Lower Senior') || decode ( F.ACAD_LEVEL_BOT, '45', 'Upper Senior') || decode ( F.ACAD_LEVEL_BOT, '52', 'Second Degree') || decode ( F.ACAD_LEVEL_BOT, 'GR', 'Graduate')
The decode function helps to determine the academic level for students based on their ACAD_LEVEL_BOT number.
CASE WHEN F.TOT_TRNSFR > 0 THEN 'Y' ELSE 'N' END
If the Total Transfer is grater than 0 then 'Y' else 'N'
LISTAGG(CASE WHEN M.STDNT_GROUP IN ('IEXP') THEN 'IEXP' WHEN M.STDNT_GROUP IN ('IESN') THEN 'IESN' WHEN M.STDNT_GROUP IN ('ISS') THEN 'ISS' WHEN M.STDNT_GROUP is null then 'Not Provided' ELSE 'OTHER' END, ', ') WITHIN GROUP (ORDER BY CASE WHEN M.STDNT_GROUP IN ('IEXP') THEN 'IEXP' WHEN M.STDNT_GROUP IN ('IESN') THEN 'IESN' WHEN M.STDNT_GROUP IN ('ISS') THEN 'ISS' WHEN M.STDNT_GROUP is null then 'Not Provided' ELSE 'OTHER' END)
Listtagg function helps to fetch multiple values in a single row. it's an aggregate function. In this example, the report determine student based on their student group with multiple value such as 'IEXP', 'IESN', 'ISS'. Student may contain more than one value. Therefore, Listtagg resolve the occurrence of duplications in the report.
Prompt
Institution, Career, Term have been mandatory for an user to input and Degree, ACAD_PROG, ACAD_PLAN are optional to extract more precise data.
Criteria:
ACAD_PROG_STATUS is 'AC' because the report is based on active students. Added 2 service indicator records to define 'APS' and 'ACP' with their reason code through SERV_IND_DATA record. Create join with ACAD_PLAN with ACAD_SUB_PLAN to define the major concentration. Added STNDT_GRPS to get student group information.
Query Output Image:
SQL Summery:
SELECT DISTINCT A.EMPLID, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, C.ADMIT_TYPE, A.ADMIT_TERM, A.ACAD_CAREER, A.ACAD_PROG, B.PLAN_SEQUENCE, B.ACAD_PLAN, I.ACAD_SUB_PLAN, J.ACAD_PLAN, K.ACAD_SUB_PLAN, G.DESCR, G.DEGREE, A.DEGR_CHKOUT_STAT, G.SSR_NSC_CRD_LVL, decode ( F.ACAD_LEVEL_BOT, '00', 'Not Set') || decode ( F.ACAD_LEVEL_BOT, '11', 'Lower Freshman') || decode ( F.ACAD_LEVEL_BOT, '15', 'Upper Freshman')|| decode ( F.ACAD_LEVEL_BOT, '21', 'Lower Sophmore') || decode ( F.ACAD_LEVEL_BOT, '25', 'Upper Sophmore')|| decode ( F.ACAD_LEVEL_BOT, '31', 'Lower Junior') || decode ( F.ACAD_LEVEL_BOT, '35', 'Upper Junior') || decode ( F.ACAD_LEVEL_BOT, '41', 'Lower Senior') || decode ( F.ACAD_LEVEL_BOT, '45', 'Upper Senior') || decode ( F.ACAD_LEVEL_BOT, '52', 'Second Degree') || decode ( F.ACAD_LEVEL_BOT, 'GR', 'Graduate'), F.UNT_TAKEN_PRGRSS, F.TOT_CUMULATIVE, F.TOT_PASSD_GPA, F.CUM_GPA, D.PHONE, D.EMAIL_ADDR, E.SRVC_IND_CD || ' ' || E.SRVC_IND_REASON, H.SRVC_IND_CD || ' ' || H.SRVC_IND_REASON, F.TOT_TRNSFR, CASE WHEN F.TOT_TRNSFR > 0 THEN 'Y' ELSE 'N' END, LISTAGG(CASE WHEN M.STDNT_GROUP IN ('IEXP') THEN 'IEXP' WHEN M.STDNT_GROUP IN ('IESN') THEN 'IESN' WHEN M.STDNT_GROUP IN ('ISS') THEN 'ISS' when M.STDNT_GROUP is null then 'Not Provided' ELSE 'OTHER' END, ', ') WITHIN GROUP (ORDER BY CASE WHEN M.STDNT_GROUP IN ('IEXP') THEN 'IEXP' WHEN M.STDNT_GROUP IN ('IESN') THEN 'IESN' WHEN M.STDNT_GROUP IN ('ISS') THEN 'ISS' when M.STDNT_GROUP is null then 'Not Provided' ELSE 'OTHER' END),G.INSTITUTION,G.ACAD_PLAN,TO_CHAR(G.EFFDT,'YYYY-MM-DD') FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, (SELECT TC.ADMIT_TYPE, TC.EMPLID, TC.ACAD_CAREER, TC.STDNT_CAR_NBR, TC.ADM_APPL_NBR, TC.INSTITUTION FROM PS_ADM_APPL_DATA TC,PS_ADM_APPL_SCTY C1 WHERE TC.EMPLID = C1.EMPLID AND TC.ACAD_CAREER = C1.ACAD_CAREER AND TC.ADM_APPL_NBR = C1.ADM_APPL_NBR AND C1.OPRCLASS = 'CUHCPPBAR01' AND C1.OPRID = '11002650' ) C, PS_CU_BIO_VW D, PS_STDNT_CAR_TERM F, PS_ACAD_PLAN_TBL G, PS_SRVC_IND_DATA E, PS_SRVC_IND_DATA H, PS_ACAD_SUBPLAN I, PS_ACAD_PLAN J, PS_ACAD_SUBPLAN K, PS_STDNT_GRPS M WHERE ( A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.ACAD_CAREER = A_ED.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR AND A_ED.EFFDT <= SYSDATE) AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.ACAD_CAREER = A_ES.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR AND A.EFFDT = A_ES.EFFDT) AND A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND A.EFFSEQ = B.EFFSEQ AND A.EFFDT = B.EFFDT AND A.INSTITUTION = :1 AND A.PROG_STATUS = 'AC' AND A.EMPLID = C.EMPLID (+) AND A.ACAD_CAREER = C.ACAD_CAREER (+) AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR (+) AND A.ADM_APPL_NBR = C.ADM_APPL_NBR (+) AND A.INSTITUTION = C.INSTITUTION (+) AND A.EMPLID = D.EMPLID AND A.EMPLID = F.EMPLID AND A.ACAD_CAREER = F.ACAD_CAREER AND A.STDNT_CAR_NBR = F.STDNT_CAR_NBR AND A.INSTITUTION = F.INSTITUTION AND B.ACAD_PLAN = G.ACAD_PLAN AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_ACAD_PLAN_TBL G_ED WHERE G.INSTITUTION = G_ED.INSTITUTION AND G.ACAD_PLAN = G_ED.ACAD_PLAN AND G_ED.EFFDT <= B.EFFDT) AND A.INSTITUTION = G.INSTITUTION AND A.ACAD_CAREER = :2 AND F.STRM = :3 AND F.UNT_TAKEN_PRGRSS > 0 AND ( G.DEGREE = :4 OR :4 IS NULL) AND ( A.ACAD_PROG = :5 OR :5 IS NULL) AND ( B.ACAD_PLAN = :6 OR :6 IS NULL) AND A.EMPLID = E.EMPLID (+) AND 'APS' = E.SRVC_IND_CD (+) AND A.INSTITUTION = E.INSTITUTION (+) AND A.EMPLID = H.EMPLID (+) AND A.INSTITUTION = H.INSTITUTION (+) AND 'ACP' = H.SRVC_IND_CD (+) AND B.EMPLID = I.EMPLID (+) AND B.ACAD_CAREER = I.ACAD_CAREER (+) AND B.STDNT_CAR_NBR = I.STDNT_CAR_NBR (+) AND B.EFFSEQ = I.EFFSEQ (+) AND B.ACAD_PLAN = I.ACAD_PLAN (+) AND B.EFFDT = I.EFFDT (+) AND B.EMPLID = J.EMPLID (+) AND B.ACAD_CAREER = J.ACAD_CAREER (+) AND B.STDNT_CAR_NBR = J.STDNT_CAR_NBR (+) AND B.EFFSEQ = J.EFFSEQ (+) AND B.ACAD_PLAN <> J.ACAD_PLAN (+) AND B.EFFDT = J.EFFDT (+) AND J.EMPLID = K.EMPLID (+) AND J.ACAD_CAREER = K.ACAD_CAREER (+) AND J.STDNT_CAR_NBR = K.STDNT_CAR_NBR (+) AND J.EFFSEQ = K.EFFSEQ (+) AND J.ACAD_PLAN = K.ACAD_PLAN (+) AND J.EFFDT = K.EFFDT (+) AND B.PLAN_SEQUENCE = (SELECT MIN ( L.PLAN_SEQUENCE) FROM PS_ACAD_PLAN L WHERE ( L.EMPLID = B.EMPLID AND L.ACAD_CAREER = B.ACAD_CAREER AND L.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND L.EFFDT = B.EFFDT AND L.EFFSEQ = B.EFFSEQ )) AND A.EMPLID = M.EMPLID (+) AND A.INSTITUTION = M.INSTITUTION (+) ) GROUP BY A.EMPLID, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, C.ADMIT_TYPE, A.ADMIT_TERM, A.ACAD_CAREER, A.ACAD_PROG, B.PLAN_SEQUENCE, B.ACAD_PLAN, I.ACAD_SUB_PLAN, J.ACAD_PLAN, K.ACAD_SUB_PLAN, G.DESCR, G.DEGREE, A.DEGR_CHKOUT_STAT, G.SSR_NSC_CRD_LVL, decode ( F.ACAD_LEVEL_BOT, '00', 'Not Set') || decode ( F.ACAD_LEVEL_BOT, '11', 'Lower Freshman') || decode ( F.ACAD_LEVEL_BOT, '15', 'Upper Freshman')|| decode ( F.ACAD_LEVEL_BOT, '21', 'Lower Sophmore') || decode ( F.ACAD_LEVEL_BOT, '25', 'Upper Sophmore')|| decode ( F.ACAD_LEVEL_BOT, '31', 'Lower Junior') || decode ( F.ACAD_LEVEL_BOT, '35', 'Upper Junior') || decode ( F.ACAD_LEVEL_BOT, '41', 'Lower Senior') || decode ( F.ACAD_LEVEL_BOT, '45', 'Upper Senior') || decode ( F.ACAD_LEVEL_BOT, '52', 'Second Degree') || decode ( F.ACAD_LEVEL_BOT, 'GR', 'Graduate'), F.UNT_TAKEN_PRGRSS, F.TOT_CUMULATIVE, F.TOT_PASSD_GPA, F.CUM_GPA, D.PHONE, D.EMAIL_ADDR, E.SRVC_IND_CD || ' ' || E.SRVC_IND_REASON, H.SRVC_IND_CD || ' ' || H.SRVC_IND_REASON, F.TOT_TRNSFR, CASE WHEN F.TOT_TRNSFR > 0 THEN 'Y' ELSE 'N' END,G.INSTITUTION,G.ACAD_PLAN,TO_CHAR(G.EFFDT,'YYYY-MM-DD') ORDER BY 2, 3, 4, 9