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

Panel

Warren,
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).

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

Greetings,

The Zicklin Dean's Office is currently working on the AACSB accreditation survey and require information via queries in CUNYfirst. We will require the following queries by Monday, April 20th, 2015 to complete the survey:

Query 3: Admissions

- Institution: BAR01

- Academic Level Term Start

- Service Indicator: "Accepted In Official Major"

-Reason: "Admitted to the Zicklin School"

-Start Term (Prompt to select term)

-Service Indicator: "Academic Plan Status" (include IF they also have a Service Indicator of "Accepted In Official Major")

-Reason: "Must Satisfy additional req"

-Start Term (Prompt to select term)

-Academic Plan: BBA plans

If you have any questions or if there is anything else needed, please let me know as soon as possible.

Thank you in advance.

Best regards,
Sarah

Later added start term, academic level, active term request by SARAH.

Final Report Delivery with requirements:

The query for Zicklin Student Enrollment CU_BAR_SR_00030_6 and Zicklin Admissions
CU_BAR_SR_00080 have 00085 has not been shared with you. Both the queries are Warren but not Mike and Patrick. The query is working fine.

CU_BAR_SR_00080 Prompts are below00085 Report includes:

Institution: Bar01 BAR01
Career: UGRD
Term From: UGRD 1156
Term To: pick any term
Degree: BBA (or pick any others)
1159
Admit Type: 2 (as freshman)
Total : 1074 (with grades and Regents date, score)AcadProg (optional) If you want to be more specific – AcadPlan (optional) If you want to be more specific

...

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_ADDRSERV_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_PASSD_GPA, F.CUM_GPA

ADMIT_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_

ACAD

ADM_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 A.EFFDT = A_ES.EFFDT)
AND A

          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

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_PROG_NBR

(+)
AND A.INSTITUTION

AND B.EFFSEQ = C.EFFSEQ
AND B.EFFDT = C.

INSTITUTION (+)

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

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

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 1

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: