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
  • Sub-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_PROGEMPLID, ADMIT TERM,ACAD_CAREER
ACAD_PLAN1ST ACAD_PLAN
CU_BIO_VW
LAST_NAME, FIRST_NAME, MIDDLE_NAME
STDNT_CAR_TERMUNT_TAKEN_PRGRSS, TOT_TAKEN_PRGRSS, TOT_PASSD_PRGRSS, CUM_GPA
ACAD_PLAN2ND ACAD_PLAN
STDNT_CAR_TERMCUR_GPA
SF_STDNT_PRS_VW SEX - Gender
ACAD_SUBPLANACAD_SUB_PLAN (it's not showing into the report but it's depends as per their request)
Records
Description of fields

 

Expressions:

 ':3' has been used to retrieve prior history (Prior Term) of active student  through STDNT_CAR_TERM. 

Prompt:

Institution, Term  for Current and Prior have been mandatory for an user to input in order to retrieve data. 

Criteria:

ACAD_PROG_STATUS is 'AC' because the report is based on active students.  The subquery include ' CU_STDNTGRP_VW' record because according to the requirement we need to exclude international executive student group from the report.  Exclude B.ACAD_PLAN NOT IN ('IOP-MS','FINENGR-MS','HCA-MBA','ILR-MS').  Added sub plan with plan in order to find 'MGTEXE-MBA' students.  Created sub query with Does not Exist to filter 'MGTEXE-MBA' group.  'Gender' field also newly added to the query.

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

  • No labels