Background:
Kevin Taylor has been requested to add some more fields and wanted to exclude some other group of students such as 'HCA-MBA, ILR-MS' from the list along with 'MGTEXE-MBA' students. 'MGTEXE-MBA' does not belong to any group. However, this is the sub plan of 'MGTBAP-MBA' plan. We did not have any kind of sample to follow the structure for this query to reach the solution.
Hi Patrick,
Thanks for the list, as its very helpful. However, I am hoping to also get a list of only new matrics (Summer and Fall 2015) with the same exclusions as listed below (no MFE, Exec, etc.). I also see some MS-ILR and MBA-HCA students listed which should be exclude on the new list.
Thanks in advance.
Kevin
Kevin,
Attached are results of CU_BAR_SR_00078 run for BAR01, Current Term = 1159, Prior Term = 1156. Includes 1,876 records.
Criteria included MBA and MS, then excludes ACAD_PLAN 'IOP-MS','FINENGR-MS', and where student group ‘IEP’ . HCA-MBA plan is not currently excluded. Nor are a couple of “LAW” plans. You should review and then we can modify.
Fields appear to correspond, except for gender field.
Review and we can discuss what additional changes are require for this query.
Thanks.
Patrick
Final Report Delivery with requirements:
The report does include followings:
- Last Name
- First Name
- Middle Name
- Empl Id
- 1st Plan
- 2nd Plan
- Career
- Admit Term
- Term Credits Taken
- Total Taken Credits
- Total Passed Credits
- Cum GPA
- Prior Term GPA
- Gender
CU_BAR_SR_00078_1 Prompts are below:
- Institution: Bar01
- Current Term: pick any term
- Prior Term: Pick any prior term (the purpose of this prompt to retrieve the history)
NOTE THAT: The reason to create term over to see history because some of student admit during the summer term but enroll for class for 'Fall Term'. Therefore, Zicklin came across this 'term range' as their solution. If someone needs to find the report for 'Spring Term' then place the term as current term and prior term both. For example, if the Spring term is 1152 then place 1152 as current and prior term in the prompt box.
Query Development:
Following table shows all necessary records that have been used to generate fields as requested by an user to create this report.
ACAD_PROG | EMPLID, ADMIT TERM,ACAD_CAREER |
ACAD_PLAN | 1ST ACAD_PLAN |
CU_BIO_VW | LAST_NAME, FIRST_NAME, MIDDLE_NAME |
STDNT_CAR_TERM | UNT_TAKEN_PRGRSS, TOT_TAKEN_PRGRSS, TOT_PASSD_PRGRSS, CUM_GPA |
ACAD_PLAN | 2ND ACAD_PLAN |
STDNT_CAR_TERM | CUR_GPA |
SF_STDNT_PRS_VW | SEX - Gender |
ACAD_SUBPLAN | ACAD_SUB_PLAN (it's not showing into the report) |
Records | Description of fields |
---|
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.
The image of CU_BAR_SR_00078_1:
Query SQL:
SELECT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, B.ACAD_PLAN, G.ACAD_PLAN, A.ACAD_CAREER, A.ADMIT_TERM, E.UNT_TAKEN_PRGRSS, E.TOT_TAKEN_PRGRSS, E.TOT_PASSD_PRGRSS, E.CUM_GPA, H.CUR_GPA, I.SEX, J.ACAD_SUB_PLAN
FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, PS_CU_BIO_VW C, PS_STDNT_CAR_TERM E, PS_ACAD_PLAN G, PS_STDNT_CAR_TERM H, PS_SF_STDNT_PRS_VW I, PS_ACAD_SUBPLAN J
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.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND A.EFFSEQ = B.EFFSEQ
AND A.EFFDT = B.EFFDT
AND A.INSTITUTION = :1
AND A.PROG_STATUS = 'AC'
AND E.STRM = :2
AND A.ACAD_PROG IN ('MBA','MS')
AND A.EMPLID = C.EMPLID
AND B.ACAD_PLAN NOT IN ('IOP-MS','FINENGR-MS','HCA-MBA','ILR-MS')
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 NOT EXISTS (SELECT D.EMPLID
FROM PS_CU_STDNTGRP_VW D
WHERE ( D.EMPLID = A.EMPLID
AND D.INSTITUTION = A.INSTITUTION
AND D.STDNT_GROUP = 'IEXP' ))
AND E.UNT_TAKEN_PRGRSS > 0
AND B.PLAN_SEQUENCE = (SELECT min ( F.PLAN_SEQUENCE)
FROM PS_ACAD_PLAN F
WHERE ( F.EMPLID = B.EMPLID
AND F.ACAD_CAREER = B.ACAD_CAREER
AND F.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND F.EFFDT = B.EFFDT
AND F.EFFSEQ = B.EFFSEQ ))
AND B.EMPLID = G.EMPLID (+)
AND B.ACAD_CAREER = G.ACAD_CAREER (+)
AND B.STDNT_CAR_NBR = G.STDNT_CAR_NBR (+)
AND B.EFFSEQ = G.EFFSEQ (+)
AND B.EFFDT = G.EFFDT (+)
AND B.PLAN_SEQUENCE < G.PLAN_SEQUENCE (+)
AND E.EMPLID = H.EMPLID (+)
AND E.ACAD_CAREER = H.ACAD_CAREER (+)
AND E.INSTITUTION = H.INSTITUTION (+)
AND :3 = H.STRM (+)
AND E.STDNT_CAR_NBR = H.STDNT_CAR_NBR (+)
AND A.EMPLID = I.EMPLID
AND B.EMPLID = J.EMPLID (+)
AND B.ACAD_CAREER = J.ACAD_CAREER (+)
AND B.STDNT_CAR_NBR = J.STDNT_CAR_NBR (+)
AND B.EFFSEQ = J.EFFSEQ (+)
AND B.ACAD_PLAN = J.ACAD_PLAN (+)
AND B.EFFDT = J.EFFDT (+)
AND NOT EXISTS (SELECT K.EMPLID
FROM PS_ACAD_SUBPLAN K
WHERE ( K.EFFDT = J.EFFDT
AND K.EMPLID = A.EMPLID
AND K.ACAD_CAREER = J.ACAD_CAREER
AND K.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND K.EFFSEQ = J.EFFSEQ
AND K.ACAD_SUB_PLAN = 'MGTEXE-MBA' )) )
ORDER BY 1, 2, 3