Background:
Sarah Leobis wanted a report for Zicklin Undergraduate BBA Enrollment.
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 1: Enrollment
o Institution: BAR01
o Academic Level (e.g. Freshman, Lower Sophomore, Upper Sophomore, etc.)
o Academic Plan: BBA plans
o Term (Prompt to select term)
o Academic load
o Sex
o Citizenship
o Ethnicity
o Career
§ Query 2: Degrees Conferred
o Institution: BAR01
o Degree
o Confer Date (Prompt to select)
o Academic Plan
o Career
o Sex
§ 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
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_00030_6 Prompts are below:
- Institution: Bar01
- Term: pick any term
- Acad Plan: pick any BBA plans
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:
Based on her request The CU_BAR_SR_00030_5 has been modified to CU_BAR_SR_00030_5A. The following steps have been followed to full fill the requirements:
- 2 following tables have been added to add the column (CITIZENSHIP)
- The SCC_CITIZEN_QVW - Citizenship Query View
- The CITIZEN_STS_TBL - Citizen Status Table
- added ACAD Load column from STDNT_CAR_TERM table
- Deleted 2 criteria as CUM GPA is higher than 3.5 and Credits earned more than 30.
Query Output:
SQL Summery:
SELECT A.EMPLID, B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, C.SEX, F.RESIDENCY, P.VISA_PERMIT_TYPE, A.ACAD_PROG, M.ACAD_PLAN, R.ACAD_SUB_PLAN, E.ACAD_PLAN, Q.ACAD_SUB_PLAN, A.ADMIT_TERM, D.UNT_TAKEN_PRGRSS, D.TOT_CUMULATIVE, D.CUM_GPA, decode ( G.ETHNIC_GROUP, '1', 'White ') ||
decode ( H.ETHNIC_GROUP, '2', 'Black ') ||
decode ( I.ETHNIC_GROUP, '3', 'Hispanic ') ||
decode ( J.ETHNIC_GROUP, '4', 'Asian ') ||
decode ( K.ETHNIC_GROUP, '5', 'AmerInd ') ||
decode ( L.ETHNIC_GROUP, '6', 'Non-Hispanic/Not-Specified '), B.PHONE, B.EMAIL_ADDR, A.ACAD_CAREER, D.ACADEMIC_LOAD, T.DESCR,T.COUNTRY,T.CITIZENSHIP_STATUS
FROM PS_ACAD_PROG A, PS_CU_BIO_VW B, PS_SF_STDNT_PRS_VW C, PS_STDNT_CAR_TERM D, PS_SCC_ETH_GRP_VW G, PS_SCC_ETH_GRP_VW H, PS_SCC_ETH_GRP_VW I, PS_SCC_ETH_GRP_VW J, PS_SCC_ETH_GRP_VW K, PS_SCC_ETH_GRP_VW L, PS_ACAD_PLAN M, PS_ACAD_PLAN E, PS_RESIDENCY_OFF F, PS_SCC_VISA_P_QVW P, PS_ACAD_SUBPLAN Q, PS_ACAD_SUBPLAN R, PS_SCC_CITIZEN_QVW S, PS_CITIZEN_STS_TBL T
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.INSTITUTION = :1
AND A.ACAD_CAREER = 'UGRD'
AND D.STRM = :2
AND A.EMPLID = B.EMPLID
AND A.EMPLID = C.EMPLID
AND A.EMPLID = D.EMPLID
AND A.ACAD_CAREER = D.ACAD_CAREER
AND A.STDNT_CAR_NBR = D.STDNT_CAR_NBR
AND D.INSTITUTION = A.INSTITUTION
AND A.EMPLID = G.EMPLID (+)
AND '1' = G.ETHNIC_GROUP (+)
AND A.EMPLID = H.EMPLID (+)
AND '2' = H.ETHNIC_GROUP (+)
AND A.EMPLID = I.EMPLID (+)
AND '3' = I.ETHNIC_GROUP (+)
AND A.EMPLID = J.EMPLID (+)
AND '4' = J.ETHNIC_GROUP (+)
AND A.EMPLID = K.EMPLID (+)
AND '5' = K.ETHNIC_GROUP (+)
AND A.EMPLID = L.EMPLID (+)
AND '6' = L.ETHNIC_GROUP (+)
AND A.EMPLID = M.EMPLID
AND A.ACAD_CAREER = M.ACAD_CAREER
AND A.STDNT_CAR_NBR = M.STDNT_CAR_NBR
AND A.EFFSEQ = M.EFFSEQ
AND A.EFFDT = M.EFFDT
AND M.EMPLID = E.EMPLID (+)
AND M.ACAD_CAREER = E.ACAD_CAREER (+)
AND M.STDNT_CAR_NBR = E.STDNT_CAR_NBR (+)
AND M.EFFSEQ = E.EFFSEQ (+)
AND M.EFFDT = E.EFFDT (+)
AND M.ACAD_PLAN <> E.ACAD_PLAN (+)
AND A.EMPLID = F.EMPLID
AND A.ACAD_CAREER = F.ACAD_CAREER
AND F.INSTITUTION = A.INSTITUTION
AND A.PROG_STATUS = 'AC'
AND D.UNT_TAKEN_PRGRSS > 0
AND M.ACAD_PLAN = :4
AND M.PLAN_SEQUENCE = (SELECT MIN ( N.PLAN_SEQUENCE)
FROM PS_ACAD_PLAN N
WHERE N.EMPLID = M.EMPLID
AND N.ACAD_CAREER = M.ACAD_CAREER
AND N.STDNT_CAR_NBR = M.STDNT_CAR_NBR
AND N.EFFDT = M.EFFDT
AND N.EFFSEQ = M.EFFSEQ)
AND F.EFFECTIVE_TERM = (SELECT MAX ( O.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF O
WHERE O.EMPLID = F.EMPLID
AND O.ACAD_CAREER = F.ACAD_CAREER
AND O.INSTITUTION = F.INSTITUTION
AND O.EFFECTIVE_TERM <= :2)
AND A.EMPLID = P.EMPLID (+)
AND 'USA' = P.COUNTRY (+)
AND E.EMPLID = Q.EMPLID (+)
AND E.ACAD_CAREER = Q.ACAD_CAREER (+)
AND E.STDNT_CAR_NBR = Q.STDNT_CAR_NBR (+)
AND E.EFFSEQ = Q.EFFSEQ (+)
AND E.ACAD_PLAN = Q.ACAD_PLAN (+)
AND E.EFFDT = Q.EFFDT (+)
AND M.EMPLID = R.EMPLID (+)
AND M.ACAD_CAREER = R.ACAD_CAREER (+)
AND M.STDNT_CAR_NBR = R.STDNT_CAR_NBR (+)
AND M.EFFSEQ = R.EFFSEQ (+)
AND M.ACAD_PLAN = R.ACAD_PLAN (+)
AND M.EFFDT = R.EFFDT (+)
AND A.EMPLID = S.EMPLID (+)
AND 'USA' = S.COUNTRY (+)
AND S.COUNTRY = T.COUNTRY (+)
AND S.CITIZENSHIP_STATUS = T.CITIZENSHIP_STATUS (+)
ORDER BY 2, 3, 4