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