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 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 |
|---|---|
| ACAD_PROG | EMPLID, ADMIT TERM,ACAD_CAREER, ACAD_PROG, |
| ACAD_PLAN | ACAD_PLAN,PLAN_SEQ, |
| ADM_APPL_DATA | ADMIT_TYPE |
| 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 | ACADEMIC LEVEL,UNT_TAKEN_PRGRSS,TOT_CUM,TOT_PASSED_GPA, CUM_GPA |
| ACAD_PLAN_TBL | DESCR, DEGREE, SSR_NSC_CRD_LVL(DEG LEVEL) |
| 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 Indicators
Prompt:
Institution, Career, Term have been mandatory for an user to input and Degree, ACAD_PROG, ACAD_PLAN are optional to extract more precise data.
Criteria:
ACAD_PROG_STATUS is 'AC' because the report is based on active 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:
SQL Summery:
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.TOT_CUMULATIVE, F.TOT_PASSD_GPA, F.CUM_GPA, 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,TO_CHAR(G.EFFDT,'YYYY-MM-DD')
FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, (SELECT TC.ADMIT_TYPE, TC.EMPLID, TC.ACAD_CAREER, TC.STDNT_CAR_NBR, TC.ADM_APPL_NBR, TC.INSTITUTION
FROM PS_ADM_APPL_DATA TC,PS_ADM_APPL_SCTY C1 WHERE TC.EMPLID = C1.EMPLID AND TC.ACAD_CAREER = C1.ACAD_CAREER AND TC.ADM_APPL_NBR = C1.ADM_APPL_NBR AND C1.OPRCLASS = 'CUHCPPBAR01' AND C1.OPRID = '11002650' ) C, PS_CU_BIO_VW D, PS_STDNT_CAR_TERM F, PS_ACAD_PLAN_TBL G, PS_SRVC_IND_DATA E, PS_SRVC_IND_DATA H, PS_ACAD_SUBPLAN I
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.ACAD_CAREER = A_ED.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.ACAD_CAREER = A_ES.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
AND A.EFFDT = A_ES.EFFDT)
AND A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND A.EFFSEQ = B.EFFSEQ
AND A.EFFDT = B.EFFDT
AND A.INSTITUTION = :1
AND A.PROG_STATUS = 'AC'
AND A.EMPLID = C.EMPLID (+)
AND A.ACAD_CAREER = C.ACAD_CAREER (+)
AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR (+)
AND A.ADM_APPL_NBR = C.ADM_APPL_NBR (+)
AND A.INSTITUTION = C.INSTITUTION (+)
AND A.EMPLID = D.EMPLID
AND A.EMPLID = F.EMPLID
AND A.ACAD_CAREER = F.ACAD_CAREER
AND A.STDNT_CAR_NBR = F.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.UNT_TAKEN_PRGRSS > 0
AND ( G.DEGREE = :4
OR :4 IS NULL)
AND ( A.ACAD_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.EMPLID (+)
AND B.ACAD_CAREER = I.ACAD_CAREER (+)
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 2, 3, 4, 11