Background:
Sarah Leobis wanted a report for Zicklin Undergraduate Accounting BBA Enrollment by term.
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: ACC-BBA
o Term (Prompt to select term)
o Academic load
o Sex
o Citizenship
o Ethnicity
o Career
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_7 haS been shared with Sarah and Patrick. The query is working fine with accurate report.
CU_BAR_SR_00030_7 Prompts are below:
- Institution: Bar01
- Term: pick any term
Query Development:
Based on her request The CU_BAR_SR_00030_6 has been modified to CU_BAR_SR_00030_7. The following steps have been followed to full fill the requirements:
Prompt:
Removed the prompt called Acad_Plan because they preferred the static data.
Criteria:
Added Acad_plan = 'ACC-BBA' to find only Acounting BBA student for a given term and see their status in report.
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 = 'ACC-BBA'
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