Background:
Sarah Leobis wanted a report for Zicklin Undergraduate Admission based on Service Indicator.
Greetings,
The Zicklin Dean's Office is currently working on the AACSB accreditation survey and require information via queries in CUNYfirst. We will require the following queries by Monday, April 20th, 2015 to complete the survey:
§ Query 3: Admissions
o Institution: BAR01
o Academic Level Term Start
o Service Indicator: "Accepted In Official Major"
§ Reason: "Admitted to the Zicklin School"
§ Start Term (Prompt to select term)
o Service Indicator: "Academic Plan Status" (include IF they also have a Service Indicator of "Accepted In Official Major")
§ Reason: "Must Satisfy additional req"
§ Start Term (Prompt to select term)
o Academic Plan: BBA plans
If you have any questions or if there is anything else needed, please let me know as soon as possible.
Thank you in advance.
Best regards,
Sarah
Later added start term, academic level, active term request by SARAH.
Final Report Delivery with requirements:
The query for Zicklin Enrollment CU_BAR_SR_00030_6 and Zicklin Admissions
CU_BAR_SR_00080 have been shared with you. Both the queries are working fine.
CU_BAR_SR_00080 Prompts are below:
- Institution: Bar01
- Career: UGRD
- Term: pick any term
- Degree: BBA (or pick any others)
- 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 |
| SERV_IND_DATA | SRVC_IND_CD for 'APS' and 'ACP' and have used for their reason codes |
| 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) |
| ACAD_SUBPLAN | ACAD_SUB_PLAN |
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
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. Create join with ACAD_PLAN with ACAD_SUB_PLAN to define the major concentration.
Query Output Image:
SQL Summery:
SELECT A.EMPLID, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, C.ADMIT_TYPE, A.ADMIT_TERM, A.ACAD_CAREER, A.ACAD_PROG, B.ACAD_PLAN, I.ACAD_SUB_PLAN, B.PLAN_SEQUENCE, G.DESCR, G.DEGREE, 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, E.SRVC_IND_ACT_TERM, H.SRVC_IND_ACT_TERM,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
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.EFFSEQ = I.EFFSEQ (+)
AND B.ACAD_PLAN = I.ACAD_PLAN (+)
AND B.STDNT_CAR_NBR = I.STDNT_CAR_NBR (+)
AND B.EFFDT = I.EFFDT (+) )
ORDER BY 2, 3, 4, 11