Background:
Margo Weaker has requested to add few fields to extent the report for students who has 'real estate' as their plan or subplan. The query should be more dynamic in terms of searching plans.
Margo,
The last communication on this request was from Tabassum Lodhi on 03/13/2015. She provided an excel file based on the query she was developing for you and asked for you to review and provide feedback. The excel file does include GPA, Sex, term and total units and admit term.
Please review the file and let us know if it contains the values you are looking for as well as all the records that should be included. If so, we can copy this query to you to run, or adjust it further based on your requirements.
Let us know if you have any questions.
Thank you.
Patrick
Final Report Delivery with requirements:
The report does include followings: Please see the kace ticket for history: https://helpdesk.baruch.cuny.edu/ ticket reference number: 37529
- GPA
- Sex
- admit term
- total units taken
- current units taken
- email address
CU_BAR_SR_00077_2 Prompts are below:
- Institution: Bar01
- Term: pick any term
- Career: UGRD (ptional)
- Acad Plan (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, |
| CU_BOI_VW | LAST_NAME, FIRST_NAME, MIDDLE_NAME, PHONE, EMAIL_ADDR |
| SF_STDNT_PRS_VW | SEX |
| STDNT_CAR_TERM | UNT_TAKEN_PRGRSS, TOT_CUMULATIVE, CUM_GPA |
| SCC_ETH_GRP_VW | decode (G.ETHNIC_GROUP, '1', 'White ') || |
| SCC_ETH_GRP_VW | decode (H.ETHNIC_GROUP, '2', 'Black ') || |
| SCC_ETH_GRP_VW | decode (I.ETHNIC_GROUP, '3', 'Hispanic ') || |
| SCC_ETH_GRP_VW | decode (J.ETHNIC_GROUP, '4', 'Asian ') || |
| SCC_ETH_GRP_VW | decode (K.ETHNIC_GROUP, '5', 'AmerInd ') || |
| SCC_ETH_GRP_VW | decode (L.ETHNIC_GROUP, '6', 'Non-Hispanic/Not-Specified ') |
| ACAD_PLAN | ACAD_PLAN |
| ACAD_PLAN | ACAD_PLAN |
| RESIDENCY_OFF | RESIDENCY ( SEE Subquery) |
| SCC_VISA_P_QVW | VISA_PERMIT_TYPE |
| ACAD_SUBPLAN | ACAD_SUB_PLAN |
| ACAD_SUBPLAN | ACAD_SUB_PLAN |
| STDNT_GRPS_HIST | STDNT_GROUP |
Expressions:
Few 'Expressions' have been added such as to find 'Ethnicity' which has described the student ethnic background. 'USA' has described the students visa status other than USA. 'A' has been used to define active student group through student hist group.
Prompt:
Institution, Term have been mandatory for an user to input and Career, ACAD_PLAN are optional to extract more precise data.
Criteria:
ACAD_PROG_STATUS is 'AC' because the report is based on active students. There is a subquery based on 'RESIDENCY_OFF' record because we would like to have current residency information. Create join with ACAD_PLAN with ACAD_SUB_PLAN to define the major concentration.
Query Output Image:
Query SQL:
SELECT B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, 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, S.STDNT_GROUP
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_STDNT_GRPS_HIST S
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 = :3
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 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 A.INSTITUTION = S.INSTITUTION (+)
AND 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' = S.EFF_STATUS(+)
AND M.ACAD_PLAN = :4 )
ORDER BY 1, 2, 3