Background:
Sarah Leobis wanted a report for Zicklin Undergraduate Enrollment based on given (prompt) term.
Greetings,
We would like a query be created similar to query CU_BAR_SR_00010, with the exception of the student groups criteria.
Specifically, we need the same idea (output student grades for a given career and term) with the following:
- Prompt for Career
- Prompt for Term
- Remove Student Groups Criteria
- Course subjects to be included (e.g. 'in-list'):
- ACC
- BPL
- BUS
- CIS
- ECO
- FIN
- IBS
- INS
- LAW
- MGT
- MKT
- OPR
- RES
- STA
- TAX
If there is any additional information required, please let me know.
Final Report Delivery with requirements:
The query for Zicklin Enrollment CU_BAR_SR_00010_1 has been copied to Sarah and Patrick. The query is working fine.
CU_BAR_SR_00010_1 Prompts are below:
- Institution: Bar01
- Career: UGRD
- Term: pick any term
Please see the detail request in kace ticket reference: https://helpdesk.baruch.cuny.edu/admin Ticket number: 45321
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 | |
STDNT_GRPS_HIST | EMPLID |
CU_BIO_VW | LAST_NAME, FIRST_NAME, MIDDLE_NAME |
CLASS_TBL_SE_VW | SUBJECT, CATALOG_NBR, DESCR, CLASS_SECTION, SESSION_CODE, CRSE_GRADE_OFF |
Expressions:
Few 'Expressions' have been deleted from original CU_BAR_SR_00010 from prompt such as 'Subject' and 'Catalog NBR' in order to receive all detail rather than a specific subject and catalog number in the report. The 'Student Group' has been delete from criteria because the report needs show all category students who belongs one or many groups
Prompt:
Institution, Career, Term have been mandatory for an user to input.
Criteria:
B.EFF_STATUS - Status as of Effective Date equal to 'A' has removed because the report is not based on active students other than it's looking for enrolled students.
Image of CU_BAR_SR_00010_1
Query SQL:
FROM PS_TERM_TBL A, PS_STDNT_GRPS_HIST B, PS_CU_BIO_VW C, PS_CLASS_TBL_SE_VW D
WHERE A.INSTITUTION = :1
AND A.ACAD_CAREER = :2
AND A.STRM = :3
AND B.INSTITUTION = A.INSTITUTION
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.INSTITUTION = B_ED.INSTITUTION
AND B.STDNT_GROUP = B_ED.STDNT_GROUP
AND B_ED.EFFDT <= A.TERM_BEGIN_DT)
AND B.EMPLID = C.EMPLID
AND B.EMPLID = D.EMPLID
AND B.INSTITUTION = D.INSTITUTION
AND D.STDNT_ENRL_STATUS = 'E'
AND D.STRM = A.STRM
AND D.SUBJECT IN ('ACC','BPL','BUS','CIS','ECO','FIN','IBS','INS','LAW','MGT','MKT','OPR','RES','STA','TAX')
ORDER BY 1, 2, 3, 5, 6, 8