CU_BAR_CM_00021_1 was created to view the Honor Class detail. However, the query were producing duplicate in some of the classes with same information.  Therefore, Jody has been requested to us to remove the duplication.

I'm following up on this ticket for your request for modification of CU_BAR_CM_00021_1 to address duplicate course listed in the results. We will complete the modification to address duplicates through removal of class days. We'll respond back with data set for you to review and confirm, and then copy the modified query back to you.

Thank you for your patience.

Patrick

The Honor Class detail query based on classes that have been instructed by same or multiple instructors in given term across in CUNYfirst.  The criteria of this query are:

  • Class detail based on current and prior semester
  • Currently enrolled
  • Class meeting pattern should be distinct

Final Report Delivery with requirements:

The query for Honors Class Detail without Duplication has been shared with Jody and Patrick.  The data has been confirmed by Jody.  The query is working fine. For Kace ticket reference: https://helpdesk.baruch.cuny.edu/admin The ticket number is: 35291

CU_BAR_CM_00021_2 Prompts are below:  

  • Institution: Bar01 
  • TermFrom: Prior Term
  •  TermTo: Current 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.

CLASS_TBL

 ACAD_GROUP, ACAD_ORG, SUBJECT, CATALOG_NBR, DESCR, CLASS_SECTION, STRM, CLASS_STAT, INSTRUCTION_MODE, ROOM_CAP_REQUEST, ENRL_TOT,

CLASS_INSTR_VW5FIRST_NAME, LAST_NAME
CU_BIO_VIEW
EMAIL_ADDR
 CLASS_MTG_PAT  FACILITY_ID, MEETING_TIME_START, MEETING_TIME_END, CLASS DAY (expression)
Records
Fields from the Record

 

Expressions:


decode (E.MON, 'Y', 'Mo') || decode (E.TUES, 'Y', 'Tu') || decode (E.WED, 'Y', 'We') || decode (E.THURS, 'Y', 'Th') || decode (E.FRI, 'Y', 'Fr') || decode (E.SAT, 'Y', 'Sa') || decode (E.SUN, 'Y', 'Su')

Days 'Expressions' have been added to find 'Class Days' meeting for an instructor of a particular class. 

Prompt:

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

Criteria:

Check Mark on Distinct through Properties link

Query Output Image:

SQL Summery:

 

SELECT DISTINCT A.ACAD_GROUP, A.ACAD_ORG, A.SUBJECT, A.CATALOG_NBR, A.DESCR, A.CLASS_SECTION, A.STRM, A.CLASS_STAT, A.INSTRUCTION_MODE, A.ROOM_CAP_REQUEST, A.ENRL_TOT, E.FACILITY_ID, TO_CHAR(CAST((E.MEETING_TIME_START) AS TIMESTAMP),'HH24.MI.SS.FF'), TO_CHAR(CAST((E.MEETING_TIME_END) AS TIMESTAMP),'HH24.MI.SS.FF'), B.LAST_NAME, B.FIRST_NAME, C.EMAIL_ADDR, decode ( E.MON, 'Y', 'Mo') || decode ( E.TUES, 'Y', 'Tu') || decode ( E.WED, 'Y', 'We') || decode ( E.THURS, 'Y', 'Th') || decode ( E.FRI, 'Y', 'Fr') || decode ( E.SAT, 'Y', 'Sa') || decode ( E.SUN, 'Y', 'Su'), TO_CHAR(SYSDATE,'YYYY-MM-DD'),A.CRSE_ID,A.CRSE_OFFER_NBR,A.SESSION_CODE

  FROM PS_CLASS_TBL A, PS_CLASS_MTG_PAT E, PS_CLASS_INSTR_VW5 B, PS_CU_BIO_VIEW C

  WHERE A.INSTITUTION = :1

     AND A.STRM BETWEEN :2 AND :3

     AND A.CRSE_ID =  E.CRSE_ID (+)

     AND A.CRSE_OFFER_NBR =  E.CRSE_OFFER_NBR (+)

     AND A.STRM =  E.STRM (+)

     AND A.SESSION_CODE =  E.SESSION_CODE (+)

     AND A.CLASS_SECTION =  E.CLASS_SECTION (+)

     AND E.CRSE_ID =  B.CRSE_ID (+)

     AND E.CRSE_OFFER_NBR =  B.CRSE_OFFER_NBR (+)

     AND E.STRM =  B.STRM (+)

     AND E.SESSION_CODE =  B.SESSION_CODE (+)

     AND E.CLASS_SECTION =  B.CLASS_SECTION (+)

     AND E.CLASS_MTG_NBR =  B.CLASS_MTG_NBR (+)

     AND A.CATALOG_NBR LIKE '%H'

     AND B.EMPLID =  C.EMPLID (+)

  ORDER BY 1, 2, 3, 4, 6

 

Other Desire Modification:



  • No labels