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.

ACADEMIC LEVEL,,TOT_CUM,TOT_PASSED_GPA, CUM_GPA

Records	Description of fields
ACADADM_APPL_PROGDATA	ADMIT_TYPE
ADM_APPL_PROG	EMPLID, ACAD_PROG, ADMIT_TERM	EMPLID, ADMIT TERM,ACAD_CAREER, ACAD_PROG,	ACAD_PLAN	ACAD_PLAN,PLAN_SEQ,
ADM_APPL_DATAPLAN	ADMIT ACAD_ TYPE PLAN
CU_BOI_VW	LAST_NAME, FIRST_NAME, MIDDLE_NAME, PHONE, EMAIL_ADDR
SERV_IND_DATA	SRVC_IND_CD for 'APS' and 'ACP' and have used for their reason codes
STDNT_CAR_TERM	UNT_TAKEN_PRGRSS
ACAD_PLAN_TBL	DESCR, DEGREE, SSR_NSC_CRD_LVL(DEG LEVEL)

CLASS_TBL_SE_VW	SUBJECT, CATALOG_NBR , CLASS_SECTION, CRSE_GRADE_OFF
STDNT_TEST_COMP	TEST_ID, TEST_COMPONENT, TEST_DT, SCORE	ACAD_SUBPLAN	ACAD_SUB_PLAN

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.

Query Output Image:

Image Removed

SQL Summery:

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:

Image Added

SQL Summery:

Panel

SELECT DISTINCT B.EMPLID, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, B.ACAD_PROG, C.ACAD_PLAN, E

Panel

SELECT A.EMPLID, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, C.ADMIT_TYPE, A.ADMIT_TERM, A.ACAD_CAREER, A.ACAD_PROG, B.ACAD_PLAN, I.ACAD_SUB_PLAN, B.PLAN_SEQUENCE, G.DESCR, G.DEGREE, G.SSR_NSC_CRD_LVL, decode ( F.ACAD_LEVEL_BOT, '00', 'Not Set') ||
decode ( F.ACAD_LEVEL_BOT, '11', 'Lower Freshman') ||
decode ( F.ACAD_LEVEL_BOT, '15', 'Upper Freshman')||
decode ( F.ACAD_LEVEL_BOT, '21', 'Lower Sophmore') ||
decode ( F.ACAD_LEVEL_BOT, '25', 'Upper Sophmore')|| decode ( F.ACAD_LEVEL_BOT, '31', 'Lower Junior') ||
decode ( F.ACAD_LEVEL_BOT, '35', 'Upper Junior') || decode ( F.ACAD_LEVEL_BOT, '41', 'Lower Senior') ||
decode ( F.ACAD_LEVEL_BOT, '45', 'Upper Senior') ||
decode ( F.ACAD_LEVEL_BOT, '52', 'Second Degree') ||
decode ( F.ACAD_LEVEL_BOT, 'GR', 'Graduate'), F

.UNT_TAKEN_PRGRSS,

F

B.

TOT

ADMIT_

CUMULATIVE

TERM,

F

A.

TOT

ADMIT_

PASSD_GPA, F.CUM_GPA,

TYPE, D.PHONE, D.EMAIL_ADDR,

E.SRVC_IND_CD || ' ' || E.SRVC_IND_REASON, H.SRVC_IND_CD || ' ' || H.SRVC_IND_REASON, E.SRVC_IND_ACT_TERM, H.SRVC_IND_ACT_TERM,G.INSTITUTION,G.ACAD_PLAN

F.SUBJECT, F.CATALOG_NBR, F.CLASS_SECTION, F.CRSE_GRADE_OFF, G.TEST_ID, G.TEST_COMPONENT, TO_CHAR(G.

EFFDT

TEST_DT,'YYYY-MM-DD'), G.SCORE
FROM PS_ADM_

ACAD

APPL_

PROG

DATA A, PS_ADM_

ACAD

APPL_

PLAN B

SCTY A1, (SELECT

TC.ADMIT_TYPE, TC.EMPLID, TC

TB.EMPLID, TB.ACAD_PROG, TB.ADMIT_TERM, TB.ACAD_CAREER,

TC

TB.STDNT_CAR_NBR,

TC

TB.ADM_APPL_NBR, TB.EFFDT, TB.PROG_ACTION, TB.APPL_PROG_NBR,

TC

TB.EFFSEQ, TB.INSTITUTION
FROM PS_ADM_APPL_

DATA TC

PROG TB,PS_ADM_

APPL

MAINT_SCTY

C1

B1 WHERE

TC

TB.EMPLID =

C1

B1.EMPLID AND

TC

TB.ACAD_CAREER =

C1

B1.ACAD_CAREER AND

TC

TB.STDNT_CAR_NBR = B1.STDNT_CAR_NBR AND TB.ADM_APPL_NBR =

C1

B1.ADM_APPL_NBR AND

C1

TB.APPL_PROG_NBR = B1.APPL_PROG_NBR AND B1.OPRCLASS = 'CUHCPPBAR01'

AND C1

AND B1.OPRID = '11002650' ) B, PS_ADM_APPL_PLAN C, PS_CU_BIO_VW D, PS_STDNT_CAR_TERM

F

E, PS_

ACAD_PLAN

CLASS_TBL

G, PS

_

SRVC_IND_DATA E

SE_VW F, PS_

SRVC

STDNT_

IND_DATA H, PS_ACAD_SUBPLAN I

TEST_COMP G
WHERE ( A.

EFFDT

EMPLID =

(SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
WHERE A.EMPLID = A_ED.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 =

A_ED

B.ACAD_CAREER
AND A.STDNT_CAR_NBR =

A_ED

B.STDNT_CAR_NBR
AND A

_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =

.ADM_APPL_NBR = B.ADM_APPL_NBR
AND B.EFFDT =
(SELECT MAX(

A

B_

ES

ED.

EFFSEQ

EFFDT) FROM PS_ADM_

ACAD

APPL_PROG

A

B_

ES

ED
WHERE

A

B.EMPLID =

A

B_

ES

ED.EMPLID
AND

A

B.ACAD_CAREER =

A

B_

ES

ED.ACAD_CAREER
AND

A

B.STDNT_CAR_NBR =

A

B_

ES

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

A

B_ED.EFFDT

= A

<= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.

EFFDT)
AND A

EFFSEQ) FROM PS_ADM_APPL_PROG B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND

A

B.ACAD_CAREER = B_ES.ACAD_CAREER
AND

A

B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR

AND A.EFFSEQ = B.EFFSEQ
AND A

          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

A.INSTITUTION = :1
AND A

B.ADMIT_TERM BETWEEN :2 AND :3
AND ( B.PROG_

STATUS

ACTION = '

AC

MATR')
AND

A

B.EMPLID = C.EMPLID

(+)

AND

A

B.ACAD_CAREER = C.ACAD_CAREER

(+)

AND

A

B.STDNT_CAR_NBR = C.STDNT_CAR_NBR

(+)

AND

A

B.ADM_APPL_NBR = C.ADM_APPL_NBR
AND B.APPL_PROG_NBR = C.APPL_

NBR (+)
AND A.INSTITUTION = C.INSTITUTION (+)

PROG_NBR
     AND B.EFFSEQ = C.EFFSEQ
     AND B.EFFDT = C.EFFDT
     AND A.EMPLID = D.EMPLID
     AND

A

B.EMPLID =

F

E.EMPLID
AND

A

B.ACAD_CAREER =

F

E.ACAD_CAREER
AND

A

B.STDNT_CAR_NBR =

F

E.STDNT_CAR_NBR

AND A.INSTITUTION = F.INSTITUTION
AND B.ACAD_PLAN = G.ACAD_PLAN
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_ACAD_PLAN_TBL G_ED
WHERE G.INSTITUTION = G_ED.INSTITUTION
AND G.ACAD_PLAN = G_ED.ACAD_PLAN
AND G_ED.EFFDT <= B.EFFDT)
AND A.INSTITUTION = G.INSTITUTION
AND A.ACAD_CAREER = :2
AND F.STRM = :3
AND F.

     AND E.INSTITUTION = B.INSTITUTION
     AND E.UNT_TAKEN_PRGRSS > 0
     AND

( G

E.

DEGREE

STRM = :

4
OR :4 IS NULL)
AND (

3
AND A.

ACAD

ADMIT_

PROG = :5
OR :5 IS NULL)
AND ( B.ACAD_PLAN = :6
OR :6 IS NULL)
AND A.EMPLID = E.EMPLID (+)
AND 'APS' = E.SRVC_IND_CD (+)
AND A.INSTITUTION = E.INSTITUTION (+)
AND A.EMPLID = H.EMPLID (+)
AND A.INSTITUTION = H.INSTITUTION (+)
AND 'ACP' = H.SRVC_IND_CD (+)
AND B.EMPLID = I

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

B

( G.

ACAD_CAREER = I.ACAD_CAREER

TEST_ID (+)

AND B.EFFSEQ = I.EFFSEQ (+)
AND B.ACAD_PLAN = I.ACAD_PLAN (+)
AND B.STDNT_CAR_NBR = I.STDNT_CAR_NBR (+)
AND B.EFFDT = I.EFFDT (+) )
ORDER BY

= 'RGNTS'
AND G.TEST_COMPONENT (+) IN ('SMA3','MATHB')) ))
ORDER BY 6, 2, 3, 4

, 11

Page tree

Versions Compared

Old Version 2

New Version Current

Key

Background:

Final Report Delivery with requirements:

Query Development:

Expressions:

Prompt:

Criteria:

Query Output Image:

SQL Summery:

Query Prompt:

Query Output Image:

SQL Summery: