Background:
Sarah Godwin wanted to modify the query on CU_BAR_SR_00054_1 with additional fields. However, the query was modified based on CU_BAR_SR_00054_4 because this was more preferable what she was looking for. As per our conversation we (Sarah and Tabassum) both agreed to modify CU_BAR_SR_00054_4.
Hi Patrick,
We’re hoping you can help us add some fields to one of our commonly used reports here at SPA.
The report name/number is CU_BAR_SR_00054_1
The fields we’d like added are:
Address (street1, street2, city, state, zip, country)
Total credits completed
Total credits attempted
Sub-plan
Full Time/Part time
Residency
Visa status
Please let us know if it is possible to add these fields to this report.
Thank you!
Sarah
The query CU_BAR_SR_00054_5, was based on CU_BAR_SR_00054_4 and included the following additional fields:
- Address (Street, street2, City, State, Zip)
- Total credits completed
- Total credits attempted
- Sub-plan
- Full/part time
- Residency
- Visa
Please see the ticket in Kace system for reference: https://kbox.baruch.cuny.edu/userui/ticket?ID=36296
Image of CU_BAR_SR_00054_5:
Query SQL:
SELECT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, B.ACAD_PROG, D.ACAD_PLAN, A.UNT_TAKEN_PRGRSS, B.ADMIT_TERM, C.EMAIL_ADDR, C.PHONE, A.EMPLID, B.DEGR_CHKOUT_STAT, A.CUR_GPA, A.CUM_GPA, B.PROG_STATUS, B.COMPLETION_TERM, A.ACAD_LOAD_APPR, A.ACADEMIC_LOAD, E.ACAD_SUB_PLAN, H.ADDRESS1, H.ADDRESS2, H.CITY, H.STATE, H.POSTAL, I.RESIDENCY, A.ACAD_PROG_PRIMARY, CASE
WHEN A.UNT_TAKEN_PRGRSS BETWEEN 12 AND 30 THEN 'Full'
WHEN A.UNT_TAKEN_PRGRSS BETWEEN 0.01 AND 11.99 THEN 'Part'
ELSE ' '
END, K.VISA_PERMIT_TYPE, F.COUNTRY, G.COUNTRY, A.TOT_TAKEN_PRGRSS, A.TOT_PASSD_PRGRSS, TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM PS_STDNT_CAR_TERM A, PS_ACAD_PROG B, PS_CU_BIO_VW C, PS_ACAD_PLAN D, PS_ACAD_SUBPLAN E, PS_ADDRESSES4_VW H, PS_RESIDENCY_OFF I, PS_SCC_VISA_P_QVW K, PS_SCC_CITIZEN_QVW F, PS_SCC_CITIZEN_QVW G
WHERE A.INSTITUTION = 'BAR01'
AND A.ACAD_CAREER = 'GRAD'
AND A.STRM = :1
AND A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.INSTITUTION = B.INSTITUTION
AND B.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PROG B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.ACAD_CAREER = B_ED.ACAD_CAREER
AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR
AND B_ED.EFFDT <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_ACAD_PROG B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.ACAD_CAREER = B_ES.ACAD_CAREER
AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR
AND B.EFFDT = B_ES.EFFDT)
AND B.ACAD_PROG IN ('MPA','MSED','ADVCT')
AND B.EMPLID = C.EMPLID
AND B.EMPLID = D.EMPLID
AND B.ACAD_CAREER = D.ACAD_CAREER
AND B.STDNT_CAR_NBR = D.STDNT_CAR_NBR
AND B.EFFSEQ = D.EFFSEQ
AND B.EFFDT = D.EFFDT
AND D.EMPLID = E.EMPLID(+)
AND D.ACAD_CAREER = E.ACAD_CAREER(+)
AND D.STDNT_CAR_NBR = E.STDNT_CAR_NBR (+)
AND D.EFFSEQ = E.EFFSEQ (+)
AND D.ACAD_PLAN = E.ACAD_PLAN (+)
AND D.EFFDT = E.EFFDT (+)
AND B.EMPLID = F.EMPLID(+)
AND 'USA' = F.COUNTRY (+)
AND B.EMPLID = G.EMPLID(+)
AND 'USA' = G.COUNTRY (+)
AND B.EMPLID = H.EMPLID(+)
AND 'MAIL' = H.ADDRESS_TYPE (+)
AND B.EMPLID = I.EMPLID
AND B.ACAD_CAREER = I.ACAD_CAREER
AND I.INSTITUTION = B.INSTITUTION
AND I.EFFECTIVE_TERM = (SELECT MAX ( J.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF J
WHERE J.EMPLID = I.EMPLID
AND J.ACAD_CAREER = I.ACAD_CAREER
AND J.INSTITUTION = I.INSTITUTION
AND J.EFFECTIVE_TERM <= :1)
AND B.EMPLID = K.EMPLID(+)
AND 'USA' = K.COUNTRY (+)
ORDER BY 4, 1, 2, 3