...
_00045_1. She is requesting some additional data in the report. Based on the additional data, some of which is undergraduate or graduate specific, it might make sense to split out the query based on career. Please follow the link for reference from Kace: https://helpdesk.baruch.cuny.edu/admin Ticket number: 49003
Final Report Delivery with requirements:
CU_BAR_SR_00045_3 Prompts are below:
- Institution: Bar01
- Career: UGRD
- Term: pick any term
- Degree: (optional) ( pick any degree)
- 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.
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 |
| 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) |
| SERV_IND_DATA | SRVC_IND_CD for 'APS' have used it with reason code |
| SERV_IND_DATA | SRVC_IND_CD for 'ACP' have used it with reason code |
| ACAD_SUBPLAN | DESCR, DEGREE, SSR_NSC_CRD_LVL(DEG LEVEL) |
| ACAD_PLAN | ACAD_PLAN |
| ACAD_SUBPLAN | ACAD_SUB_PLAN |
| STNDT_GRPS | Student Group (SG) |
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
| No Format |
|---|
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') |
The decode function helps to determine the academic level for students based on their ACAD_LEVEL_BOT number.
| No Format |
|---|
CASE
WHEN F.TOT_TRNSFR > 0 THEN 'Y' ELSE 'N'
END |
If the Total Transfer is grater than 0 then 'Y' else 'N'
| No Format |
|---|
LISTAGG(CASE WHEN M.STDNT_GROUP IN ('SEEK') THEN 'SEEK'
WHEN M.STDNT_GROUP IN ('PATH') THEN 'PATH'
WHEN M.STDNT_GROUP IN ('HPNS', 'HREG', 'PROV', 'BSCH', 'DSCH') THEN 'HONORS'
ELSE NULL END, ', ')
WITHIN GROUP (ORDER BY
CASE WHEN M.STDNT_GROUP IN ('SEEK') THEN 'SEEK'
WHEN M.STDNT_GROUP IN ('PATH') THEN 'PATH'
WHEN M.STDNT_GROUP IN ('HPNS', 'HREG', 'PROV', 'BSCH', 'DSCH') THEN 'HONORS'
ELSE NULL END) |
Listtagg function helps to fetch multiple values in a single row. it's an aggregate function. In this example, the report determine student based on their student group with multiple value such as 'SEEK', 'PATH', 'HPNS', 'HREG', 'PROV', 'BSCH', 'DSCH'. Student may contain more than one value. Therefore, Listtagg resolve the occurrence of duplications in the report.
...
- Institution: Bar01
- Career: UGRD
- Term: pick any term
- Degree: (optional) ( pick any degree)
- 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.
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.
...
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 through SERV_IND_DATA record. Create join with ACAD_PLAN with ACAD_SUB_PLAN to define the major concentration. . Added STNDT_GRPS to get student group information.
Query Output Image:
SQL Summery:
...