Background:
Patrick proposed to modify CU_BAR_SR_00071 to add missing students email. However, we create a new query based on the requirements.
Proposed Requirements:
- Enrollment greater than 0 for specified term
- Not active eff date in “PI” student group between specified term dates (provided and confirmed by Registrar)
- CAMP email is null or not @baruchmail/@BARUCHMAIL
Final Report Delivery with requirements:
The query for
Enrl Stdnt NOCamp Email not PI CU_BAR_SR_00071_1 has been shared with Patrick Ackerman and the query is working fine.
CU_BAR_SR_00071-1 Prompts are below:
- Institution: Bar01
- Term: pick any term
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 |
---|---|
STDNT_CAR_TERM | ID |
SCC_EMAIL_QVW | |
SCC_EMAIL_QVW | Campus Email |
Expressions:
Few 'Expressions' have been added to find 'campus' or 'othr' email address. The value of 'CAMP' and 'OTHR' define email address for Permit In student who does not have Baruch email address.
Prompt:
Institution, Term have been mandatory for an user to input.
Criteria:
Top Level of Query is looking for students who has unit taken for progress is grater than 0.
Sub query for does not exist is looking form 'STDNT_GRPS_HIST' record, students who are active 'A' and student group 'PI' with effective date range between '2015-05-30 AND 2015-08-14'
Sub query for does not exist is looking from 'SCC_EMAIL_QVW' record, to eliminate student who has baruch email address.
Sub query for exists is looking from 'SCC_EMAIL_QVW' record, to eliminate not only baruch email also who has combination of baruch emial addresses such as 'lower (F.EMAIL_ADDR) not like %@baruchmail%'
Query Output Image:
Legend of the Query:
Query SQL:
SELECT A.EMPLID, C.EMAIL_ADDR
FROM PS_STDNT_CAR_TERM A, PS_SCC_EMAIL_QVW C, PS_SCC_EMAIL_QVW D
WHERE A.INSTITUTION = :1
AND A.STRM = :2
AND NOT EXISTS (SELECT B.EMPLID
FROM PS_STDNT_GRPS_HIST B
WHERE B.EFFDT BETWEEN TO_DATE('2015-05-30','YYYY-MM-DD') AND TO_DATE('2015-08-14','YYYY-MM-DD')
AND A.EMPLID = B.EMPLID
AND A.INSTITUTION = B.INSTITUTION
AND B.EFF_STATUS = 'A'
AND substr( B.STDNT_GROUP,1,2) IN ('PI'))
AND A.UNT_TAKEN_PRGRSS > 0
AND A.EMPLID = C.EMPLID (+)
AND 'CAMP' = C.E_ADDR_TYPE (+)
AND A.EMPLID = D.EMPLID (+)
AND 'OTHR' = D.E_ADDR_TYPE (+)
AND ( NOT EXISTS (SELECT E.EMPLID
FROM PS_SCC_EMAIL_QVW E
WHERE E.EMPLID = A.EMPLID
AND E.E_ADDR_TYPE = 'CAMP')
OR EXISTS (SELECT F.EMPLID
FROM PS_SCC_EMAIL_QVW F
WHERE F.EMPLID = A.EMPLID
AND F.E_ADDR_TYPE = 'CAMP'
AND lower ( F.EMAIL_ADDR) NOT LIKE '%@baruchmail%'))
FROM PS_STDNT_CAR_TERM A, PS_SCC_EMAIL_QVW C, PS_SCC_EMAIL_QVW D
WHERE A.INSTITUTION = :1
AND A.STRM = :2
AND NOT EXISTS (SELECT B.EMPLID
FROM PS_STDNT_GRPS_HIST B
WHERE B.EFFDT BETWEEN TO_DATE('2015-05-30','YYYY-MM-DD') AND TO_DATE('2015-08-14','YYYY-MM-DD')
AND A.EMPLID = B.EMPLID
AND A.INSTITUTION = B.INSTITUTION
AND B.EFF_STATUS = 'A'
AND substr( B.STDNT_GROUP,1,2) IN ('PI'))
AND A.UNT_TAKEN_PRGRSS > 0
AND A.EMPLID = C.EMPLID (+)
AND 'CAMP' = C.E_ADDR_TYPE (+)
AND A.EMPLID = D.EMPLID (+)
AND 'OTHR' = D.E_ADDR_TYPE (+)
AND ( NOT EXISTS (SELECT E.EMPLID
FROM PS_SCC_EMAIL_QVW E
WHERE E.EMPLID = A.EMPLID
AND E.E_ADDR_TYPE = 'CAMP')
OR EXISTS (SELECT F.EMPLID
FROM PS_SCC_EMAIL_QVW F
WHERE F.EMPLID = A.EMPLID
AND F.E_ADDR_TYPE = 'CAMP'
AND lower ( F.EMAIL_ADDR) NOT LIKE '%@baruchmail%'))