Initially the query CU_BAR_CM_00026 was created to count the enrollment for the students who has taken CSTM 120 class previously. However, the query could not pick up some of students who has taken FSPM 121 class as a first time and received 'R, W,WU, WN, FIN or INC'.  Therefore, those students need to retake the class.

We had to create a new query because the entire logic needs to produce in different fashion.

Patrick,

I think I understand why we are missing students.  Students who take the summer immersion program course FSPM 0121 have to be counted as if they had taken CSTM 0120, as this is the immersion equivalent of CSTM 0120..  When you search for repeaters, please include these students.  I hope you can easily modify the query to include this group.  I suspect the students Jonathan identified may be in this group.

Thank you.

Warren

The student count based on CSTM 120 and FSPM 121 COURSE in CUNYfirst.  The criteria of this query are:

  • UGRAD students in (1159) or current semester
  • Currently enrolled for CSTM 120 / FSPM 121 (summer only)
  • Who have not taken either or both the courses previously
  • Received the grade as R, W, WU, WN, FIN or INC

Final Report Delivery with requirements:

The query for students enrolled in CSTM 0120 has not been shared with Warren and Patrick yet.  Waiting For a confirmation by Warren.  The query is working fine. For Kace ticket reference: https://helpdesk.baruch.cuny.edu/admin The ticket number is: 43584

CU_BAR_CM_00027 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.

Records
Fields 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
STDNT_CAR_TERM SUPPORT CLASS_TBL_SE_VW ABOVE and counting STRM
  

 

Expressions:

One 'Expressions' have been added to find 'Previous Attempts (R, W, WU)' to count the previous attempts of this particular class. 

Prompt:

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

Criteria:

A.CRSE_ID - Course ID =  '090828' (This is specifically looking for the course 'CSTM 120' attribute ), '091787' (only if FSPM exist)

Sub query: EXIST

A.CRSE_GRADE_OFF - Official Grade  in list  ('R','W','WU') (the result is filtering only 'R','W','WU' grades)

Sub query: NOT EXIST

E.CRSE_GRADE_OFF = 'P' (eliminating students who have passed already)

Query Output Image:

IDFirst NameMiddleLastCourse ListCount
16093648KamalMBashiru1119 CSTM  120 02 WU, 1112 CSTM  120 01 WU2


SQL Summery:

 
SELECT A.EMPLID, D.FIRST_NAME, D.MIDDLE_NAME, D.LAST_NAME, listagg( A.STRM || ' ' ||  A.SUBJECT || ' ' ||  A.CATALOG_NBR || ' ' ||  A.CLASS_SECTION || ' ' ||  A.CRSE_GRADE_OFF, ', ') within group (order by  A.STRM desc), COUNT( A.CRSE_ID)
  FROM PS_CLASS_TBL_SE_VW A, PS_STDNT_CAR_TERM B, PS_CU_BIO_VW D
  WHERE ( A.CRSE_ID IN ('090828','091787')
     AND A.STRM <= :1
     AND A.EMPLID = B.EMPLID
     AND A.ACAD_CAREER = B.ACAD_CAREER
     AND A.INSTITUTION = B.INSTITUTION
     AND B.STRM = :1
     AND EXISTS (SELECT C.EMPLID
  FROM PS_CLASS_TBL_SE_VW C
  WHERE ( C.STRM = :1
     AND C.CRSE_ID IN ('090828','091787')
     AND A.EMPLID = C.EMPLID
     AND A.CRSE_GRADE_OFF IN ('R','W','WU','WN') ))
     AND A.EMPLID = D.EMPLID
     AND A.INSTITUTION = :2
     AND NOT EXISTS (SELECT E.EMPLID
  FROM PS_CLASS_TBL_SE_VW E
  WHERE ( A.CRSE_ID IN ('090828','091787')
     AND A.EMPLID = E.EMPLID
     AND E.CRSE_GRADE_OFF = 'P'
     AND E.STRM = :1 )) )
  GROUP BY  A.EMPLID,  D.FIRST_NAME,  D.MIDDLE_NAME,  D.LAST_NAME
  ORDER BY 4, 2, 3

 

Other Desire Modification:



  • No labels