You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

Background:

Warren B. Gordon Professor and Chair the Department of Mathematics  wanted a report for students enrolled in CSTM 0120 course based on a calculation of prior enrollment history.

Patrick, 

I need the output in Excel  in a roster format, that is for each 
student enrolled in CSTM 0120 in a particular semester, I need 
to know the number of previous times that student took this same 
course. There is no need for grades, since each previous time 
they received a R grade. 

I will run this query just before the start of each semester, 
including summers.  Students who previously took the course do 
not get the course materials, as they already have online access 
to it, we only distribute access to first time students. In 
order to know this information, I need the above query. 

Thanks 

Warren 


Criteria(s) are:

For the calculation of previous attempts the criteria to be used will be 
previous enrollment in CSTM 0120 with grades of W, WU, or R. 


Final Report Delivery with requirements:

The query for students enrolled in CSTM 0120 has been shared with Warren and Patrick.  The query is working fine. 

CU_BAR_CM_00026 Prompts are below:  

  • Institution: Bar01 
  • Term: 1156 or next term

Query Development:

Following table shows all necessary records that have been used to generate fields as requested by an user to create this report.

RecordsFields from the Record
CLASS_TBL_SE_VWEMPLID,SUBJECT,CATALOG_NBR, DESCR,CLASS_SECTION, SESSION_CODE,CLASS_NBR, ACAD_GROUP, GRADE_DT
CU_BIO_VWFIRST_NAME, LAST_NAME, MIDDLE_NAME
CLASS_TBL_SE_VWSUPPORT CLASS_TBL_SE_VW ABOVE
  

 

Expressions:

Few 'Expressions' have been added to find 'Academic Level' have described the prompt as null and define the value of 'ACP' and 'APS'.  Reference for Service Indicator: Service Indicators

Prompt:

Institution, Term have been mandatory for an user to input to extract data

Criteria:

in progress

Query Output Image:

SQL Summery:

 
SELECT A.EMPLID, B.FIRST_NAME, B.MIDDLE_NAME, B.LAST_NAME, A.SUBJECT, A.CATALOG_NBR, A.DESCR, A.CLASS_SECTION, A.SESSION_CODE, A.CLASS_NBR, A.ACAD_GROUP, count ( C.CLASS_NBR), TO_CHAR(A.GRADE_DT,'YYYY-MM-DD'),A.ACAD_CAREER,A.INSTITUTION,A.STRM
FROM PS_CLASS_TBL_SE_VW A, PS_CU_BIO_VW B, PS_CLASS_TBL_SE_VW C
WHERE A.INSTITUTION = :1
AND A.STRM = :2
AND A.CRSE_ID = '090828'
AND B.EMPLID = A.EMPLID
AND A.STDNT_ENRL_STATUS = 'E'
AND A.CRSE_GRADE_OFF = ' '
AND A.EMPLID = C.EMPLID
AND A.ACAD_CAREER = C.ACAD_CAREER
AND A.INSTITUTION = C.INSTITUTION
AND A.STRM >= C.STRM
AND C.CRSE_GRADE_OFF IN ('R','W','WU')
AND A.CRSE_ID = C.CRSE_ID
GROUP BY A.EMPLID, B.FIRST_NAME, B.MIDDLE_NAME, B.LAST_NAME, A.SUBJECT, A.CATALOG_NBR, A.DESCR, A.CLASS_SECTION, A.SESSION_CODE, A.CLASS_NBR, A.ACAD_GROUP, TO_CHAR(A.GRADE_DT,'YYYY-MM-DD'),A.ACAD_CAREER,A.INSTITUTION,A.STRM
ORDER BY 4, 2, 3

 

Other Desire Modification:

 

  • No labels