Background:
Daniel Battista has been requested to add some more fields and wanted to remove duplication from existed report. Based on his criteria I have created a new query while kept the original requirements and added more to meet the current requirements to meet their goal for SPA. For detail email information please see the kace: //helpdesk.baruch.cuny.edu the ticket number is 48970
Final Report Delivery with requirements:
The report does include followings:
- EmplID
- LastName
- FirstName
- MiddleName
- Program
- Plan
- Sub-Plan
- TermCredits
- AdmitTerm
- CampusEmail
- PreferredPhone
- ExpGradTerm
- ChkoutStat
- TermGPA
- CumGPA
- Status
- MailingAddress1
- Address2
- City
- State
- Postal
- Residency
- TotCompCredits
- TotAttCredits
Newly added below: - Sex
- AcadLoad(Full/Parttime)
- AcadLoad
- ComplTerm
- PrimProg
- Country
- CitizenshipStatus
- Country
- CitizenshipStatus
- Ethnicity
- VisPmtType
CU_BAR_SR_00054_6 Prompts are below:
- Term: pick any term
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,EXP_GRAD_TERM, DEGR_CHKOUT_STAT, PROG_STATUS |
ACAD_PLAN | ACAD_PLAN |
ACAD_SUBPLAN | ACAD_SUB_PLAN |
STDNT_CAR_TERM | UNT_TAKEN_PRGRSS, TOT_TAKEN_PRGRSS, TOT_PASSD_PRGRSS, ACAD_LOAD_APPR , ACADEMIC_LOAD, ACAD_PROG_PRIMARY |
SCC_EMAIL_QVW | 'CAMP' mail thru expression |
ADDRESSES4_VW | ADDRESS1, ADDRESS2, CITY, STATE, POSTAL |
RESIDENCY_OFF | RESIDENCY |
SF_STDNT_PRS_VW | SEX - Gender |
SCC_PERS_PH_QVW | PHONE |
NAME_CURR_VW | LAST_NAME, FIRST_NAME, MIDDLE_NAME |
SCC_CITIZEN_QVW | COUNTRY (USA) |
SCC_CITIZEN_QVW | COUNTRY (OTHER) |
CITIZEN_STS_TBL | DESCR (USA) |
CITIZEN_STS_TBL | DESCR (OTHER) |
SCC_ETH_GRP_VW | 'ETHNICITY' thru expression |
SCC_VISA_P_QVW | VisaPmtType |
Expressions:
Expression | Explanation |
---|---|
'CAMP' | Find campus email |
'MAIL' | Find other email |
'Y' | Find preferred phone number |
'PRI' | Find primary email |
'USA' | Find USA status /other than USA |
'USA' | Find visa permit type status |
LISTAGG(CASE WHEN P.ETHNIC_GROUP IN ('1') THEN 'WHITE' WHEN P.ETHNIC_GROUP IN ('2') THEN 'BLACK' WHEN P.ETHNIC_GROUP IN ('3') THEN 'HISPANIC' WHEN P.ETHNIC_GROUP IN ('4') THEN 'ASIAN' WHEN P.ETHNIC_GROUP IN ('5') THEN 'AMERIND' WHEN P.ETHNIC_GROUP IN ('6') THEN 'NON-HISPANIC/NOT-SPECIFIED' when P.ETHNIC_GROUP is null then 'Not Provided' ELSE 'OTHER' END, ', ') WITHIN GROUP (ORDER BY CASE WHEN P.ETHNIC_GROUP IN ('1') THEN 'WHITE' WHEN P.ETHNIC_GROUP IN ('2') THEN 'BLACK' WHEN P.ETHNIC_GROUP IN ('3') THEN 'HISPANIC' WHEN P.ETHNIC_GROUP IN ('4') THEN 'ASIAN' WHEN P.ETHNIC_GROUP IN ('5') THEN 'AMERIND' WHEN P.ETHNIC_GROUP IN ('6') THEN 'NON-HISPANIC/NOT-SPECIFIED' when P.ETHNIC_GROUP is null then 'Not Provided' ELSE 'OTHER' END) | Find Ethnicity |
Prompt:
Term for Current or Prior have been mandatory for an user to input in order to retrieve data.
Criteria:
ACAD_PROG_STATUS has not been included 'AC' because the report is based on everyone who is active and enrolled in a MPA, MSED or ADVCT program. The sub-query include ' RESIDENCY_OFF ' record because according to the requirement we need to find latest mailing address information for the report. Different kind of email address have been added to the query based on requirements. Student can have one or more ethnic background. Therefore, Ethnicity function has been custom made and calling through expression into report. 'Gender', 'Country', 'Citizenship Status', 'ACAD Load(Full/Part time)', ' Completion term' and 'Primary Program' fields also newly added to the query.
The image of CU_BAR_SR_00054_6:
Query SQL
SELECT A.EMPLID, B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.ACAD_PROG, C.ACAD_PLAN, D.ACAD_SUB_PLAN, E.UNT_TAKEN_PRGRSS, A.ADMIT_TERM, F.EMAIL_ADDR, K.PHONE, A.EXP_GRAD_TERM, A.DEGR_CHKOUT_STAT, E.CUR_GPA, E.CUM_GPA, A.PROG_STATUS, G.ADDRESS1, G.ADDRESS2, G.CITY, G.STATE, G.POSTAL, H.RESIDENCY, E.TOT_PASSD_PRGRSS, E.TOT_TAKEN_PRGRSS, J.SEX, E.ACAD_LOAD_APPR, E.ACADEMIC_LOAD, A.COMPLETION_TERM, E.ACAD_PROG_PRIMARY, L.COUNTRY, N.DESCR, M.COUNTRY, O.DESCR, LISTAGG(CASE WHEN P.ETHNIC_GROUP IN ('1') THEN 'WHITE'
WHEN P.ETHNIC_GROUP IN ('2') THEN 'BLACK'
WHEN P.ETHNIC_GROUP IN ('3') THEN 'HISPANIC'
WHEN P.ETHNIC_GROUP IN ('4') THEN 'ASIAN'
WHEN P.ETHNIC_GROUP IN ('5') THEN 'AMERIND'
WHEN P.ETHNIC_GROUP IN ('6') THEN 'NON-HISPANIC/NOT-SPECIFIED'
when P.ETHNIC_GROUP is null then 'Not Provided'
ELSE 'OTHER' END, ', ') WITHIN GROUP (ORDER BY
CASE WHEN P.ETHNIC_GROUP IN ('1') THEN 'WHITE'
WHEN P.ETHNIC_GROUP IN ('2') THEN 'BLACK'
WHEN P.ETHNIC_GROUP IN ('3') THEN 'HISPANIC'
WHEN P.ETHNIC_GROUP IN ('4') THEN 'ASIAN'
WHEN P.ETHNIC_GROUP IN ('5') THEN 'AMERIND'
WHEN P.ETHNIC_GROUP IN ('6') THEN 'NON-HISPANIC/NOT-SPECIFIED'
when P.ETHNIC_GROUP is null then 'Not Provided'
ELSE 'OTHER' END), Q.VISA_PERMIT_TYPE, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'),N.COUNTRY,N.CITIZENSHIP_STATUS,O.COUNTRY,O.CITIZENSHIP_STATUS
FROM PS_ACAD_PROG A, PS_ACAD_PLAN C, PS_ACAD_SUBPLAN D, PS_STDNT_CAR_TERM E, PS_SCC_EMAIL_QVW F, PS_ADDRESSES4_VW G, PS_RESIDENCY_OFF H, PS_SF_STDNT_PRS_VW J, PS_SCC_PERS_PH_QVW K, PS_NAME_CURR_VW B, PS_SCC_CITIZEN_QVW L, PS_SCC_CITIZEN_QVW M, PS_CITIZEN_STS_TBL N, PS_CITIZEN_STS_TBL O, PS_SCC_ETH_GRP_VW P, PS_SCC_VISA_P_QVW Q
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.ACAD_CAREER = 'GRAD'
AND A.INSTITUTION = 'BAR01'
AND A.ACAD_PROG IN ('ADVCT','MPA','MSED')
AND A.EMPLID = C.EMPLID
AND A.ACAD_CAREER = C.ACAD_CAREER
AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
AND A.EFFSEQ = C.EFFSEQ
AND A.EFFDT = C.EFFDT
AND C.EMPLID = D.EMPLID (+)
AND C.ACAD_CAREER = D.ACAD_CAREER (+)
AND C.STDNT_CAR_NBR = D.STDNT_CAR_NBR (+)
AND C.EFFSEQ = D.EFFSEQ (+)
AND C.ACAD_PLAN = D.ACAD_PLAN (+)
AND C.EFFDT = D.EFFDT (+)
AND A.EMPLID = E.EMPLID
AND A.ACAD_CAREER = E.ACAD_CAREER
AND A.STDNT_CAR_NBR = E.STDNT_CAR_NBR
AND E.INSTITUTION = A.INSTITUTION
AND E.STRM = :1
AND A.EMPLID = F.EMPLID (+)
AND 'CAMP' = F.E_ADDR_TYPE (+)
AND A.EMPLID = G.EMPLID (+)
AND 'MAIL' = G.ADDRESS_TYPE (+)
AND A.EMPLID = H.EMPLID
AND A.ACAD_CAREER = H.ACAD_CAREER
AND H.INSTITUTION = A.INSTITUTION
AND H.EFFECTIVE_TERM = (SELECT MAX ( I.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF I
WHERE ( I.EMPLID = H.EMPLID
AND I.ACAD_CAREER = H.ACAD_CAREER
AND I.INSTITUTION = H.INSTITUTION
AND I.EFFECTIVE_TERM <= :1 ))
AND A.EMPLID = J.EMPLID
AND A.EMPLID = K.EMPLID (+)
AND 'Y' = K.PREF_PHONE_FLAG (+)
AND A.EMPLID = B.EMPLID (+)
AND 'PRI' = B.NAME_TYPE (+)
AND A.EMPLID = L.EMPLID (+)
AND 'USA' = L.COUNTRY (+)
AND A.EMPLID = M.EMPLID (+)
AND 'USA' <> M.COUNTRY (+)
AND L.COUNTRY = N.COUNTRY (+)
AND L.CITIZENSHIP_STATUS = N.CITIZENSHIP_STATUS (+)
AND M.COUNTRY = O.COUNTRY (+)
AND M.CITIZENSHIP_STATUS = O.CITIZENSHIP_STATUS (+)
AND A.EMPLID = P.EMPLID (+)
AND A.EMPLID = Q.EMPLID (+)
AND 'USA' = Q.COUNTRY (+) )
GROUP BY A.EMPLID, B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.ACAD_PROG, C.ACAD_PLAN, D.ACAD_SUB_PLAN, E.UNT_TAKEN_PRGRSS, A.ADMIT_TERM, F.EMAIL_ADDR, K.PHONE, A.EXP_GRAD_TERM, A.DEGR_CHKOUT_STAT, E.CUR_GPA, E.CUM_GPA, A.PROG_STATUS, G.ADDRESS1, G.ADDRESS2, G.CITY, G.STATE, G.POSTAL, H.RESIDENCY, E.TOT_PASSD_PRGRSS, E.TOT_TAKEN_PRGRSS, J.SEX, E.ACAD_LOAD_APPR, E.ACADEMIC_LOAD, A.COMPLETION_TERM, E.ACAD_PROG_PRIMARY, L.COUNTRY, N.DESCR, M.COUNTRY, O.DESCR, Q.VISA_PERMIT_TYPE,N.COUNTRY,N.CITIZENSHIP_STATUS,O.COUNTRY,O.CITIZENSHIP_STATUS
ORDER BY 2, 3, 4