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.
| Panel |
|---|
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:
| Panel |
|---|
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 |