Versions Compared

Old Version 1

changes.mady.by.user Tabassum Lodhi

Saved on

compared with

New Version Current

changes.mady.by.user Tabassum Lodhi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Final Report Delivery with requirements:

The query for 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 

...

Institution, Term have been mandatory for an user to input.

Criteria:

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.  Create join with ACAD_PLAN with ACAD_SUB_PLAN to define the major concentration. 

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:

Image Added

 

Query SQL:

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

Future Goal:

To add another prompt called 'Effective Date From and Effective Date To' though STDNT_GRP_HIST record in the query.  In this is case it will help the query to retrieve data within a certain range of date.

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

Panel

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.

Panel

The new query CU_BAR_SR_00071_2 has been copied to you as per the 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 today based on effective date from May 30, 2015 to August 14, 2015.