Background:
Sarah Loebis wanted Zicklin enrolled student report for 1159 based on following selections:
- ID
- Last Name
- First Name
- Cumulative GPA
- Total Credits
- Total Transfer Credits
- Total Credits Attempted at Baruch
- Total Credits Earned at Baruch
- Total Graded Units
- Total Grade Points
- Major
- Amount of credits they are registered for that term
- All subjects grade
New request for adding additional fields to the query by sep 20, 2016. see following:
- Sex,
- Residency,
- Visa,
- Ethnicities,
- Citizenship
This is a request for a new CUNYFirst query that is needed for the Zicklin Program. The prompts would be: Institution, Career, Term & Student Group.
The information needed for the query see below:
Hi Sarah,
Please find attached report. The report includes:
Active and enrolled students
Term: 1159
Career: UGRD
Academic Plan: BBA (courses belong to zicklin)
Due to the active vs enrollment the attendance of students may vary because keep that mind all active students are not enrolled always.
Please see the Kace ticket as a reference:https://helpdesk.baruch.cuny.edu/admin Ticket reference: 45335
Final Report Delivery with requirements:
The query for Zicklin student CU_BAR_SR_00084 has been shared with Sarah. The query is working fine.
CU_BAR_SR_00084 Prompts are below:
- Institution: Bar01
- Career: UGRD
Term: 1159 (It's constant at this point)
The query needs to be scheduled in order to get a report. Since it carries a large data set therefore, before change or modify any thing please be sure of the query detail.
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 |
---|---|
TERM_TBL | EMPID |
ACAD_PROG | EMPID, ACAD_PROG, EFFDT, PROG_STATUS , ADMIT_TERM, EXP_GRAD_TERM |
ACAD_PLAN | ACAD_PLAN |
CU_BIO_VW | LAST_NAME, FIRST_NAME, EMAIL |
EMAIL_FERPA_VW | EMAIL_ADDR |
STDNT_CAR_TERM | CUM_GPA, UNT_TAKEN_PRGRSS |
CLASS_TBL_SE_VW | CLASS_NB, SUBJECT, CATALOG_NBR, SESSION_CODE, CRSE_GRADE_OFF |
SF_STDNT_PRS_VW | SEX |
SCC_VISA_P_QVW | VisPmtType |
SCC_ETH_GRP_VW | Ethnicity |
RESIDENCY_OFF | Residency |
Expressions:
'CAMP' |
to_char (F.CUM_GPA, '99.9999') |
to_char (F.UNT_TAKEN_FA, '999.9999') |
'USA' |
LISTAGG(CASE WHEN J.ETHNIC_GROUP IN ('1') THEN 'WHITE' WHEN J.ETHNIC_GROUP IN ('2') THEN 'BLACK' WHEN J.ETHNIC_GROUP IN ('3') THEN 'HISPANIC' WHEN J.ETHNIC_GROUP IN ('4') THEN 'ASIAN' WHEN J.ETHNIC_GROUP IN ('5') THEN 'AMERIND' WHEN J.ETHNIC_GROUP IN ('6') THEN 'NON-HISPANIC/NOT-SPECIFIED' when J.ETHNIC_GROUP is null then 'Not Provided' ELSE 'OTHER' END, ', ') WITHIN GROUP (ORDER BY CASE WHEN J.ETHNIC_GROUP IN ('1') THEN 'WHITE' WHEN J.ETHNIC_GROUP IN ('2') THEN 'BLACK' WHEN J.ETHNIC_GROUP IN ('3') THEN 'HISPANIC' WHEN J.ETHNIC_GROUP IN ('4') THEN 'ASIAN' WHEN J.ETHNIC_GROUP IN ('5') THEN 'AMERIND' WHEN J.ETHNIC_GROUP IN ('6') THEN 'NON-HISPANIC/NOT-SPECIFIED' when J.ETHNIC_GROUP is null then 'Not Provided' ELSE 'OTHER' END) |
Prompt:
Institution, Term and Career have been mandatory for an user to input.
Criteria:
EFF_STATUS is equal to A.TERM_BEGIN_DT - Term Begin Date (EffSeq = Last) because the report is based on active and enrolled students. Include majors from ACAD_PLAN like '%-BBA' . PROG_STATUS - Academic Program Status from ACAD_PROG is 'AC'. STDNT_ENRL_STATUS - Student Enrollment Status from CLASS_TBL_SE_VW is 'E'.