Background:

Sarah Leobis wanted a report for Zicklin Undergraduate Enrollment based on given (prompt) term.

Greetings,

We would like a query be created similar to query CU_BAR_SR_00010, with the exception of the student groups criteria.

Specifically, we need the same idea (output student grades for a given career and term) with the following:

  • Prompt for Career
  • Prompt for Term
  • Remove Student Groups Criteria
  • Course subjects to be included (e.g. 'in-list'):
  1.  ACC
  2.  BPL
  3.  BUS
  4.  CIS
  5.  ECO
  6.  FIN
  7.  IBS
  8.  INS
  9.  LAW
  10.  MGT
  11.  MKT
  12.  OPR
  13.  RES
  14.  STA
  15.  TAX

If there is any additional information required, please let me know.

 

Final Report Delivery with requirements:

The query for Zicklin Enrollment CU_BAR_SR_00010_1 has been copied to Sarah and Patrick. The query is working fine. 

CU_BAR_SR_00010_1 Prompts are below:  

  • Institution: Bar01 
  • Career: UGRD 
  • Term: pick any term 

Please see the detail request in kace ticket reference: https://helpdesk.baruch.cuny.edu/admin Ticket number: 45321

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
TERM_TBL  
STDNT_GRPS_HISTEMPLID
CU_BIO_VWLAST_NAME, FIRST_NAME, MIDDLE_NAME
CLASS_TBL_SE_VWSUBJECT, CATALOG_NBR, DESCR, CLASS_SECTION, SESSION_CODE, CRSE_GRADE_OFF

Expressions:

Few 'Expressions' have been deleted from original CU_BAR_SR_00010 from prompt such as 'Subject' and 'Catalog NBR' in order to receive all detail rather than a specific subject and catalog number in the report. The 'Student Group' has been delete from criteria because the report needs show all category students who belongs one or many groups  

Prompt:

Institution, Career, Term have been mandatory for an user to input.

Criteria:

B.EFF_STATUS - Status as of Effective Date equal to 'A' has removed because the report is not based on active students other than it's looking for enrolled students. 

Image of CU_BAR_SR_00010_1

Query SQL:

SELECT DISTINCT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, B.EMPLID, D.SUBJECT, D.CATALOG_NBR, D.DESCR, D.CLASS_SECTION, D.SESSION_CODE, D.CRSE_GRADE_OFF,D.EMPLID,D.ACAD_CAREER,D.INSTITUTION,D.STRM,D.CLASS_NBR
  FROM PS_TERM_TBL A, PS_STDNT_GRPS_HIST B, PS_CU_BIO_VW C, PS_CLASS_TBL_SE_VW D
  WHERE A.INSTITUTION = :1
     AND A.ACAD_CAREER = :2
     AND A.STRM = :3
     AND B.INSTITUTION = A.INSTITUTION
     AND B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED
        WHERE B.EMPLID = B_ED.EMPLID
          AND B.INSTITUTION = B_ED.INSTITUTION
          AND B.STDNT_GROUP = B_ED.STDNT_GROUP
          AND B_ED.EFFDT <= A.TERM_BEGIN_DT)
     AND B.EMPLID = C.EMPLID
     AND B.EMPLID = D.EMPLID
     AND B.INSTITUTION = D.INSTITUTION
     AND D.STDNT_ENRL_STATUS = 'E'
     AND D.STRM = A.STRM
     AND D.SUBJECT IN ('ACC','BPL','BUS','CIS','ECO','FIN','IBS','INS','LAW','MGT','MKT','OPR','RES','STA','TAX')
  ORDER BY 1, 2, 3, 5, 6, 8
  • No labels