Page History

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

...

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

...

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 'Academic Level' have described the prompt as null and define the value of 'ACP' and 'APS'. Reference for Service Indicator: Service IndicatorsREGENTS' math test score.

Image Added

TEST_ID and TEST_COMPONENT have been used as left outer join in different manner to find 'SMA3' and 'RGNTS'.

Prompt:

Institution, Career, Term From and Term To have been mandatory for an user to input and Degree, ACAD_PROG, ACAD_PLAN are optional to extract more precise to retrieve data.

Criteria:

ACAD_PROG_STATUS CAREER is 'ACUGRD' because the report is based on active undergradute students. Added 2 service indicator records to define 'APS' and 'ACP' with their reason code. Create join with ACAD_PLAN with ACAD_SUB_PLAN to define the major concentration. 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:

Image Added

Query Output Image:

SQL Summery:

Panel

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

...

Page tree

Versions Compared

Old Version 3

New Version Current

Key