Background:
Sarah Leobis wanted a report for Zicklin Undergraduate Admission based on Service Indicator.
Panel |
---|
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.
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.
...
- 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:
Panel |
---|
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') || |
...