Modification:

The modified version name DATE_CU_BAR_SR_00071_1 which has been shared with Patrick.  Now waiting for a response from him.  Please follow the link for CU_BAR_SR_00071_2

Patrick,

The query DATE_CU_BAR_SR_00071_1 has been copied to you.  Please verify and confirm me the data.   I have checked it and looks fine to me.  The effective dates as prompt have been added to view.  I will change the query name once you confirm.  Thank you.

 

The new query CU_BAR_SR_00071_2 has been created as per requirements have been provided.

  • Definition has been updated as ‘ dates are for active permit in student group’.
  • Modification of  the prompts have been added and labeled the To and From fields as “PI Stu Grp Active - Beg Date” and “PI Stu Grp Active - End Date”.
  • The total 129 rows return as of 8/1/215 based on effective date from May 30, 2015 to August 14, 2015.

Patrick affirmed the query results and it's functionalists.

 

 

Query Development:

Prompt:

Institution, Term ,PI Stu Grp Active - Beg Date  and PI Stu Grp Active - End Date (the dates were new added) 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 'beginning and end date' by calling prompt :3 and :4

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%'

The image of CU_BAR_SR_00071_2:

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 ( A.EMPLID = B.EMPLID

     AND A.INSTITUTION = B.INSTITUTION

     AND B.EFF_STATUS = 'A'

     AND substr( B.STDNT_GROUP,1,2) IN ('PI')

     AND B.EFFDT BETWEEN TO_DATE(:3,'YYYY-MM-DD') AND TO_DATE(:4,'YYYY-MM-DD') ))

     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%' ))) )

 

 

  • No labels