Background:

Gary Hotko from testing and evaluation wanted a report to add a field to CU_BAR_CM_00015 – Grades Posted After Date query based on SmartEvals “Course Number” (as is calculated in the CU_BAR_SR_00034_5 query).  The Kace ticket reference please see the link: https://helpdesk.baruch.cuny.edu/  Ticket number: 40838

 

Could you please have a variant of CU_BAR_CM_00015 – Grades Posted After Date to include the SmartEvals “Course Number” (as is calculated in the CU_BAR_SR_00034_5 query) created. 

Thank you 

Gary Hotko 

 Final Report Delivery with Requirements:

The new query named CU_BAR_00015_1 has been copied to Gary. The course number detail has been added to this. While you run the query it might take little time to retrieve data. However, you may schedule it as well. Its working fine.

CU_BAR_CM_00015_1 prompts are below:

  • Institution: BAR01
  • Term: prior term
  • GraduationDate: pick any date
  • Grade1:
  • Grade2:
  • Grade3:
  • Grade4:
  • Grade5:

 


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
CLASS_TBL_SE_VWEMPLID,ACAD_CAREER, ACAD_PROG, ACAD_GROUP, SUBJECT, CATALOG_NBR, CLASS_SECTION, SESSION_CODE, DESCR, UNT_TAKEN, CRSE_GRADE_OFF, GRADE_DT, Crouse Number (decode)
CU_BOI_VWLAST_NAME, FIRST_NAME, MIDDLE_NAME


Expressions:

 An 'Expressions' has been added to find 'Course Number' to describe the combination of STERM, CRSE_ID and CLASS_SECTION fields as 'Course Number'

decode (SUBSTR (A.STRM, 4, 1), '2', 'Sp-') ||
decode (SUBSTR (A.STRM, 4, 1), '6', 'Su-') ||
decode (SUBSTR (A.STRM, 4, 1), '9', 'Fa-') ||
SUBSTR (A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION

Prompt:

Institution,Term, GraduationDate have been mandatory for an user to input and Grade1, Grade2, Grade3, Grade4, Grade5 are optional to extract more precise data (but  prefer to insert grade). 

 Criteria:

STDNT_ENRL_STATUS is 'E' because the report is based on ENROLLED students.   

Query Output Image below:

 

Query SQL:

SELECT B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, A.ACAD_CAREER, A.ACAD_PROG, A.ACAD_GROUP, A.SUBJECT, A.CATALOG_NBR, A.CLASS_SECTION, A.SESSION_CODE, A.DESCR, A.UNT_TAKEN, A.CRSE_GRADE_OFF, TO_CHAR(A.GRADE_DT,'YYYY-MM-DD'), decode (SUBSTR ( A.STRM, 4, 1), '2', 'Sp-') ||
decode (SUBSTR ( A.STRM, 4, 1), '6', 'Su-') ||
decode (SUBSTR ( A.STRM, 4, 1), '9', 'Fa-') ||
SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION,A.INSTITUTION,A.STRM,A.CLASS_NBR
FROM PS_CLASS_TBL_SE_VW A, PS_CU_BIO_VW B
WHERE ( A.INSTITUTION = :1
AND A.STRM = :2
AND A.GRADE_DT > TO_DATE(:3,'YYYY-MM-DD')
AND A.STDNT_ENRL_STATUS = 'E'
AND A.CRSE_GRADE_OFF IN (:4,:5,:6,:7,:8)
AND A.EMPLID = B.EMPLID )
ORDER BY 1, 2, 3, 8, 9, 10

  • No labels