Background:
Warren Gordon from Math department wanted a report based on Freshman Undergraduate Students with enrollment of (CSTM 0120, MTH 1030, MTH 2003, MTH 2207, or MTH 2610) during Fall 2015 (1159) with Regents (SMA3 or MATHB) . Please see the ticket from KACE: https://helpdesk.baruch.cuny.edu/admin Ticket: 56099
When do you need this data?
Is this specific data likely a one-time, or would you require this data set again for another term?
Thanks.
Patrick
Notes on Request:
Warren had previously requested math placement scores for enrolled students - https://helpdesk.baruch.cuny.edu/userui/ticket.php?ID=45318
Data provided was from the CU_BAR_AD_0006A and CU_BAR_AD_0006B series queries.
In this case Warren is looking for new freshman (admit type 2 for 1156 or 1159) enrolled in (CSTM 0120, MTH 1030, MTH 2003, MTH 2207, or MTH 2610) during Fall 2015 (1159), with grades each student received in the course and what their math regent exam type and score (no regent data if no record exists).
Regents math score code ‘SMA3’ and ‘MATHB’ was confirmed by OTE previously. Looking at the data the enrollment of student with (CSTM 0120, MTH 1030, MTH 2003, MTH 2207, or MTH 2610) during Fall 2015 (1159) with Regents are very few. OTE will help in the future for accuracy to set up the new set of codes for Regents.
Final Report Delivery with requirements:
The query for Student Enrollment CU_BAR_SR_00085 has not been shared with Warren but not Mike and Patrick. The query is working fine.
CU_BAR_SR_00085 Report includes:
- Institution: BAR01
- Career: UGRD
- Term From: 1156
- Term To: 1159
- Admit Type: 2 (as freshman)
- Total : 1074 (with grades and Regents date, score)
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 |
---|---|
ADM_APPL_DATA | ADMIT_TYPE |
ADM_APPL_PROG | EMPLID, ACAD_PROG, ADMIT_TERM |
ADM_APPL_PLAN | ACAD_PLAN |
CU_BOI_VW | LAST_NAME, FIRST_NAME, MIDDLE_NAME, PHONE, EMAIL_ADDR |
STDNT_CAR_TERM | UNT_TAKEN_PRGRSS |
CLASS_TBL_SE_VW | SUBJECT, CATALOG_NBR , CLASS_SECTION, CRSE_GRADE_OFF |
STDNT_TEST_COMP | TEST_ID, TEST_COMPONENT, TEST_DT, SCORE |
Expressions:
Few 'Expressions' have been added to find 'REGENTS' math test score.
TEST_ID and TEST_COMPONENT have been used as left outer join in different manner to find 'SMA3' and 'RGNTS'.
Prompt:
Institution, Term From and Term To have been mandatory for an user to input to retrieve data.
Criteria:
ACAD_CAREER is 'UGRD' because the report is based on undergradute students. PROG_ACTION is 'MATR' matriculated to find current enrollment of a given semester.
AND B.EMPLID = G.EMPLID (+)
AND ( G.TEST_ID (+) = 'RGNTS'
AND G.TEST_COMPONENT (+) IN ('SMA3','MATHB')) ))
The left outer join has been defined differently to pull Regents with other math courses such as ('090828','093130','093135','093157','093160') .
Query Prompt:
Query Output Image:
SQL Summery:
SELECT DISTINCT B.EMPLID, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, B.ACAD_PROG, C.ACAD_PLAN, E.UNT_TAKEN_PRGRSS, B.ADMIT_TERM, A.ADMIT_TYPE, D.PHONE, D.EMAIL_ADDR, F.SUBJECT, F.CATALOG_NBR, F.CLASS_SECTION, F.CRSE_GRADE_OFF, G.TEST_ID, G.TEST_COMPONENT, TO_CHAR(G.TEST_DT,'YYYY-MM-DD'), G.SCORE FROM PS_ADM_APPL_DATA A, PS_ADM_APPL_SCTY A1, (SELECT TB.EMPLID, TB.ACAD_PROG, TB.ADMIT_TERM, TB.ACAD_CAREER, TB.STDNT_CAR_NBR, TB.ADM_APPL_NBR, TB.EFFDT, TB.PROG_ACTION, TB.APPL_PROG_NBR, TB.EFFSEQ, TB.INSTITUTION FROM PS_ADM_APPL_PROG TB,PS_ADM_MAINT_SCTY B1 WHERE TB.EMPLID = B1.EMPLID AND TB.ACAD_CAREER = B1.ACAD_CAREER AND TB.STDNT_CAR_NBR = B1.STDNT_CAR_NBR AND TB.ADM_APPL_NBR = B1.ADM_APPL_NBR AND TB.APPL_PROG_NBR = B1.APPL_PROG_NBR AND B1.OPRCLASS = 'CUHCPPBAR01' AND B1.OPRID = '11002650' ) B, PS_ADM_APPL_PLAN C, PS_CU_BIO_VW D, PS_STDNT_CAR_TERM E, PS_CLASS_TBL_SE_VW F, PS_STDNT_TEST_COMP G WHERE ( A.EMPLID = A1.EMPLID AND A.ACAD_CAREER = A1.ACAD_CAREER AND A.ADM_APPL_NBR = A1.ADM_APPL_NBR AND A1.OPRCLASS = 'CUHCPPBAR01' AND A1.OPRID = '11002650' AND ( A.INSTITUTION = :1 AND A.ACAD_CAREER = 'UGRD' AND A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND A.ADM_APPL_NBR = B.ADM_APPL_NBR AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_ADM_APPL_PROG B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.ACAD_CAREER = B_ED.ACAD_CAREER AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR AND B.ADM_APPL_NBR = B_ED.ADM_APPL_NBR AND B.APPL_PROG_NBR = B_ED.APPL_PROG_NBR AND B_ED.EFFDT <= SYSDATE) AND B.EFFSEQ = (SELECT MAX(B_ES.EFFSEQ) FROM PS_ADM_APPL_PROG B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.ACAD_CAREER = B_ES.ACAD_CAREER AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR AND B.ADM_APPL_NBR = B_ES.ADM_APPL_NBR AND B.APPL_PROG_NBR = B_ES.APPL_PROG_NBR AND B.EFFDT = B_ES.EFFDT) AND B.ADMIT_TERM BETWEEN :2 AND :3 AND ( B.PROG_ACTION = 'MATR') AND B.EMPLID = C.EMPLID AND B.ACAD_CAREER = C.ACAD_CAREER AND B.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND B.ADM_APPL_NBR = C.ADM_APPL_NBR AND B.APPL_PROG_NBR = C.APPL_PROG_NBR AND B.EFFSEQ = C.EFFSEQ AND B.EFFDT = C.EFFDT AND A.EMPLID = D.EMPLID AND B.EMPLID = E.EMPLID AND B.ACAD_CAREER = E.ACAD_CAREER AND B.STDNT_CAR_NBR = E.STDNT_CAR_NBR AND E.INSTITUTION = B.INSTITUTION AND E.UNT_TAKEN_PRGRSS > 0 AND E.STRM = :3 AND A.ADMIT_TYPE = '2' AND B.EMPLID = F.EMPLID AND B.ACAD_CAREER = F.ACAD_CAREER AND F.INSTITUTION = B.INSTITUTION AND F.CRSE_ID IN ('090828','093130','093135','093157','093160') AND B.EMPLID = G.EMPLID (+) AND ( G.TEST_ID (+) = 'RGNTS' AND G.TEST_COMPONENT (+) IN ('SMA3','MATHB')) )) ORDER BY 6, 2, 3, 4 |