/*
This was put together by Wyatt Watson, Director of Institutional Research at Arkansas Tech University

Special thanks to:
	Steve Rea and Bruce Knox at UAEX for all thier help even if they didn't know it 
		(some of this code was taken from thier websites and only modified slightly (if at all))
	Mingguang Xu and Denise Gardner at the University of Georgia IR Office
		(They provided help with Oracle dates in a SAIR presentation in 2007)
*/







--Format a persons name and find the spriden_id
 SELECT f_format_name(A.sgbstdn_pidm,'LFMI') AS "Name",
    f_getspridenid(A.sgbstdn_pidm)           AS "T-Number"
   FROM sgbstdn A
  WHERE A.sgbstdn_pidm = 314
    /* This routine formats a spriden name into one of several types: */
    /* LF30 - Last name, first name for 30 characters            */
    /* L30 -  Last name for 30 characters                        */
    /* FL30 - First name, last name for 30 characters            */
    /* FL   - Last name, first name                              */
    /* FMIL - First name, middle initial, last name              */
    /* FML  - First name, middle name, last name                 */
    /* LFMI - Last name, first name, middle initial              */
    ;
        
    
    
--Select a students classification
 SELECT floor(dbms_random.value(100001, 999999)) AS pidm,
    Z.sgbstdn_term_code_eff                             ,
    F_CLASS_CALC_FNC(Z.sgbstdn_pidm, Z.sgbstdn_levl_code, Z.sgbstdn_term_code_eff) AS "Class"
   FROM sgbstdn Z ;
   
   
   
--Select the descritpion of styp_code instead of the actual code
 SELECT floor(dbms_random.value(100001, 999999)) AS pidm,
    Z.sgbstdn_term_code_eff                             ,
    Z.sgbstdn_styp_code                                 ,
    f_student_get_desc('STVSTYP',Z.sgbstdn_styp_code,30) AS "Student Type"
   FROM sgbstdn Z
    /*
    This function can read descriptions from the following validation tables:
    STVCNTY, STVNATN, STVSBGI, STVRESD, STVSESS, STVCAMP, STVDEPT, STVLEVL, STVDEGC, STVCOLL, STVMAJR, STVETHN,
    STVLGCY, STVCTYP, STVTERM, STVHLDD, STVPTRM, STVSCHD, STVSUBJ, STVESTS, STVCITZ, STVRECR, STVRSTA, STVWRSN,
    STVRTYP, STVPTYP, STVINTS, STVMATL, STVADMR, STVADMT, STVAPDC, STVSTYP, STVSITE, STVRATE, STVEGOL, STVEDLV,
    STVDPLM, GTVLETR, STVADVR, STVRSTS, STVGMOD, STVCLAS, STVFCST, STVFCTG, STVFSTP, STVNIST, STVFATT, STVASTD,
    STVDEGS, STVACYR, STVGRST, STVDIVS, STVSTST, STVARTP, STVBLDG, STVMRCD, STVHAPS, STVRRCD, STVASCD, STVPRCD,
    STVPSCD, STVMSCD, STVAPST, STVACTC, STVSPST, STVELIG, STVRELT
    There are other description functions for other tables
    */
    ;
    
    
    
--Find a cumulative GPA as of a point in time
 SELECT floor(dbms_random.value(100001, 999999))                                                                                              AS pidm                       ,
    f_student_get_desc('STVCLAS',f_class_calc_fnc(A.sgbstdn_pidm, A.sgbstdn_levl_code,'200870'),30)                                           AS "Classification"           ,
    to_number(f_split_fields((SUBSTR(f_concat_as_of_cum_gpa (A.sgbstdn_pidm,'200870',A.sgbstdn_levl_code,'O', A.sgbstdn_camp_code),1,84)),5)) AS "Historical Cumulative GPA",
    f_concat_as_of_cum_gpa (A.sgbstdn_pidm,'200870',A.sgbstdn_levl_code,'O', A.sgbstdn_camp_code)                                             AS function_value
    /*
    The function f_concat_as_of_cum_gpa returns a string that includes many data points all concatenated together.
    I am pulling the first 84 characters of the string which is greater than the max number of characters that could be returned
    The returned string is delimited by a {
    The f_split_fields function is used to retrieve a single data point from the return string, in this case data point #5
    The 'O' (not zero) is returning the overall GPA information - you could use 'I' or 'T' for institutional or transfer
    data point 1 = hours attempted
    data point 2 = hours earned
    data point 3 = gpa hours
    data point 4 = quality points
    data point 5 = gpa
    data point 6 = hours passed
    */
   FROM sgbstdn A
  WHERE
    --A.sgbstdn_term_code_eff = (select max(B.sgbstdn_term_code_eff) from sgbstdn B
    --                           where A.sgbstdn_pidm = B.sgbstdn_pidm
    --                           and B.sgbstdn_term_code_eff <= 200870)
    --and EXISTS (select 'X' from sfrstcr
    --        where sfrstcr_term_code = '200870'
    --        and sfrstcr_rsts_code in('RE','RW')
    --        and sfrstcr_pidm = A.sgbstdn_pidm)
    A.rowid = f_get_sgbstdn_rowid(A.sgbstdn_pidm,'200870')
    AND f_registered_this_term(A.sgbstdn_pidm,'200870') = 'Y' ;
    
    
    
--Select registered or enrolled students
 SELECT floor(dbms_random.value(100001, 999999)) AS pidm,
    A.sgbstdn_majr_code_1
   FROM sgbstdn A
  WHERE A.rowid = f_get_sgbstdn_rowid(A.sgbstdn_pidm,'200920')
AND ( f_registered_this_term(A.sgbstdn_pidm,'200870') = 'Y'
    /*stvrsts_incl_sect_enrl = 'Y'*/
 OR f_enrolled_this_term(A.sgbstdn_pidm,'200870') = 'Y'
    /*stvests_eff_headcount is not null*/
    ) ;
    
    
        
--Find all current student's email addresses
 SELECT SUBSTR(goremal_email_address,1,1)
    || '@atu.edu' AS email
   FROM goremal,
    spbpers
  WHERE goremal_pidm IN
                (SELECT DISTINCT sfrstcr_pidm
                 FROM sfrstcr
                WHERE sfrstcr_term_code = '200920'
                AND sfrstcr_rsts_code    IN('RE','RW')
                )
    -- f_registered_this_term(goremal_pidm,'200920') = 'Y' /*this can be used instead of the above line*/
    AND spbpers_pidm (+)     = goremal_pidm
    AND (SPBPERS_CONFID_IND IS NULL
        OR SPBPERS_CONFID_IND   = 'N')
    AND (SPBPERS_DEAD_IND   IS NULL
        OR SPBPERS_DEAD_IND     = 'N')
    AND goremal.rowid = f_get_email_rowid(goremal_pidm,'ATU_EMAIL','A',1) ;
    
    
    
--Look up grades
 SELECT floor(dbms_random.value(100001, 999999)) AS pidm,
    A.SHRTCKG_GRDE_CODE_FINAL                           ,
    DECODE(A.SHRTCKG_GRDE_CODE_FINAL,'A','Successful','A*','Successful','B','Successful', 
                                    'B*','Successful','C','Successful','C*','Successful', 
                                    'CR','Successful','P','Successful','AU','Audit', 
                                    'R','Registered','NG','Not Graded','IP','In Progress', 
                                    'D','Unsuccessful','D*','Unsuccessful','F','Unsuccessful', 
                                    'F*','Unsuccessful','FE','Unsuccessful','I','Unsuccessful', 
                                    'W','Unsuccessful','WF','Unsuccessful', 'Other') AS outcome
   FROM SHRTCKG A,
    SHRTCKN B
  WHERE A.shrtckg_pidm    = B.shrtckn_pidm
AND A.shrtckg_term_code   = B.shrtckn_term_code
AND A.shrtckg_tckn_seq_no = B.shrtckn_seq_no
AND A.shrtckg_seq_no      =
    (SELECT MAX(C.shrtckg_seq_no)
       FROM shrtckg C
      WHERE A.shrtckg_pidm    = C.shrtckg_pidm
    AND A.shrtckg_term_code   = C.shrtckg_term_code
    AND A.shrtckg_tckn_seq_no = C.shrtckg_tckn_seq_no
    ) ;
    
/* alternatively do the following */

 SELECT floor(dbms_random.value(100001, 999999)) AS pidm,
    A.SHRTCKG_GRDE_CODE_FINAL                           ,
    DECODE(A.SHRTCKG_GRDE_CODE_FINAL,'A','Successful','A*','Successful','B','Successful', 
                                    'B*','Successful','C','Successful','C*','Successful', 
                                    'CR','Successful','P','Successful','AU','Audit', 
                                    'R','Registered','NG','Not Graded','IP','In Progress', 
                                    'D','Unsuccessful','D*','Unsuccessful','F','Unsuccessful', 
                                    'F*','Unsuccessful','FE','Unsuccessful','I','Unsuccessful', 
                                    'W','Unsuccessful','WF','Unsuccessful', 'Other') AS outcome
   FROM SHRTCKG A,
    SHRTCKN B
  WHERE A.rowid = f_get_shrtckg_rowid(B.shrtckn_pidm,B.shrtckn_term_code,B.shrtckn_seq_no,1) ;
  
  
  
--Generate an email list for use in Outlook
WITH emaildata AS
    (SELECT email_addr                            ,
        row_number() over (order by email_addr) rn,
        COUNT(*) over () cnt
       FROM
        (SELECT goremal_email_address AS email_addr
           FROM goremal
          WHERE goremal_pidm = 314
        )
    )
 SELECT ltrim(sys_connect_by_path(email_addr, ';'),';') catvalues
   FROM emaildata
  WHERE rn = cnt START
WITH rn    = 1 CONNECT BY prior rn = rn-1 ;

/*Alternate*/

 SELECT ltrim(sys_connect_by_path(email_addr, ';'),';') catvalues
   FROM
    (SELECT email_addr                            ,
        row_number() over (order by email_addr) rn,
        COUNT(*) over () cnt
       FROM
        (SELECT goremal_email_address AS email_addr
           FROM goremal
          WHERE goremal_pidm = 314
        )
    )
  WHERE rn = cnt START
WITH rn    = 1 CONNECT BY prior rn = rn-1 ;



--Where does a person live and what is his/her contact home contact phone number
 SELECT floor(dbms_random.value(100001, 999999)) AS pidm,
 x.spraddr_street_line1 , x.spraddr_street_line2 , x.spraddr_street_line3 , x.spraddr_city , x.spraddr_stat_code , x.spraddr_zip , x.spraddr_natn_code ,
 x.spraddr_atyp_code , v.sprtele_unlist_ind , v.SPRTELE_TELE_CODE , v.SPRTELE_PHONE_AREA , v.SPRTELE_PHONE_NUMBER , v.SPRTELE_PHONE_EXT ,
 v.SPRTELE_STATUS_IND , y.spraddr_street_line1 , y.spraddr_street_line2 , y.spraddr_street_line3 , y.spraddr_city , y.spraddr_stat_code ,
 y.spraddr_zip , y.spraddr_natn_code , y.spraddr_atyp_code , w.sprtele_unlist_ind , w.SPRTELE_TELE_CODE , w.SPRTELE_PHONE_AREA ,
 w.SPRTELE_PHONE_NUMBER , w.SPRTELE_PHONE_EXT , w.SPRTELE_STATUS_IND
   FROM spriden,
    spraddr x  ,
    spraddr y  ,
    sprtele v  ,
    sprtele w  ,
    spbpers
  WHERE spriden_change_ind IS NULL
AND x.rowid(+) = f_get_address_rowid (spriden_pidm,'STDNADDR','A',sysdate,1,'S',NULL)
AND y.rowid(+) = f_get_address_rowid (spriden_pidm,'STDNADDR','A',sysdate,2,'S',NULL)
AND v.rowid(+) = f_get_address_telephone_rowid (spriden_pidm,'STDNADDR','A',sysdate,1,'S',NULL)
AND w.rowid(+) = f_get_address_telephone_rowid (spriden_pidm,'STDNADDR','A',sysdate,2,'S',NULL) 
AND spriden_pidm = spbpers_pidm
AND (SPBPERS_CONFID_IND IS NULL
        OR SPBPERS_CONFID_IND   = 'N')
AND (SPBPERS_DEAD_IND   IS NULL
        OR SPBPERS_DEAD_IND     = 'N');



--Find current employees
 SELECT DECODE(spbpers_name_prefix,NULL,( DECODE(spbpers_pref_first_name,NULL,spriden_first_name,spbpers_pref_first_name)    || ' '
    || spriden_last_name),(spbpers_name_prefix    || ' '
    || ( DECODE(spbpers_pref_first_name,NULL,spriden_first_name,spbpers_pref_first_name)    || ' '
    || spriden_last_name))) AS "Name"  ,
    ptrjbln_desc            AS "Office",
    goremal_email_address   AS "Email" ,
    (SELECT ( '('        || NVL(SPRTELE_PHONE_AREA,'???')        || ') '
        || NVL((SUBSTR(sprtele_phone_number,1,3)        || '-'
        || SUBSTR(sprtele_phone_number,4,4)),'???-????')) AS Office_Phone
       FROM sprtele
      WHERE sprtele.rowid = f_get_address_telephone_rowid(spriden_pidm,'OCADDR','A',sysdate,1,'S',NULL)
    ) AS "Office Phone"
   FROM spriden, spbpers, pebempl, ptrjbln, goremal
  WHERE spriden_change_ind IS NULL
AND spriden_pidm = spbpers_pidm
AND spbpers_pidm = 314
AND EXISTS
    (SELECT 'X' FROM nbrbjob, nbbposn
      WHERE spriden_pidm    = nbrbjob_pidm
    AND trunc(nbrbjob_begin_date) <= trunc(sysdate)
    AND ((trunc(nbrbjob_end_date) >= trunc(sysdate))
     OR (nbrbjob_end_date  IS NULL))
    AND nbrbjob_posn        = nbbposn_posn
    AND nbbposn_ecls_code  IN ('A1', 'A2', 'A3', 'A4', 'A5', 'B1', 'B2', 'B3', 'B4', 
                                'C1', 'C2', 'C3', 'C4', 'D1', 'D2', 'D3', 'D4')    )
AND spriden_pidm      = pebempl_pidm
AND pebempl_jbln_code = ptrjbln_code (+)
AND goremal.rowid (+) = f_get_email_rowid(spriden_pidm,'E1','A',1) ;



--How to find SSB pages in WebTailor
 SELECT TWGRMENU_NAME   ,
    TWGRMENU_SEQUENCE   ,
    TWGRMENU_URL        ,
    TWGRMENU_ENABLED    ,
    TWGRMENU_DB_LINK_IND,
    TWGRMENU_SUBMENU_IND,
    TWGRMENU_SOURCE_IND
   FROM TWGRMENU
  WHERE TWGRMENU_NAME IN
    (SELECT TWGBWMNU_NAME
       FROM TWGBWMNU
      WHERE TWGBWMNU_HEADER = 'What-if Analysis'
     OR TWGBWMNU_HEADER     = 'General Requirements'
     OR TWGBWMNU_HEADER     = 'Detail Requirements'
     OR TWGBWMNU_HEADER     = 'Additional Information'
    )
AND TWGRMENU_URL IN ('bwckcapp.P_DispCurrent','bwlkfcap.P_FacDispCurrent')
ORDER BY TWGRMENU_NAME,
    TWGRMENU_URL      ,
    TWGRMENU_SOURCE_IND ;
    
    
    
--Find a table name
 SELECT table_name,
    column_name
   FROM all_tab_cols
  WHERE SUBSTR (column_name, 9) LIKE UPPER ('%&&col_Substr%')
AND owner LIKE UPPER ('&&Ownr')
AND table_name NOT LIKE '%CVT'
ORDER BY 1;

/*Alternate*/

 SELECT table_name,
    column_name
   FROM all_tab_cols
  WHERE SUBSTR(column_name,9) LIKE '%TELE%'
AND owner <> 'BANINST1'
ORDER BY 1;

/*Alternate*/

 SELECT table_name,
    column_name
   FROM all_tab_cols
  WHERE column_name LIKE '%ATYP%'
AND owner NOT IN ('BANINST1','SCTCVT','FRSCVT','OTGMGR','SYS','SYSMAN')
ORDER BY 1,2;



--How to handle dates and times

--this is how the date is actually stored
select dump(sysdate) from dual; 

--Select a formated date
--Today's Date
select to_char(sysdate,'dd/mon/yyyy') from dual;
select to_char(sysdate,'dd/Mon/yy') from dual;
select to_char(sysdate,'dd-month-yyyy') from dual;
select to_char(sysdate,'dd/Month/yy') from dual;
select to_char(sysdate,'Dy dd Mon yy') from dual;
select to_char(sysdate,'Day dd Mon yy') from dual;
select to_char(sysdate,'Day dd-mm-yy') from dual;
select to_char(sysdate,'Day mm-dd-yy') from dual;
--The time right now
select to_char(sysdate,'hh12:mi:ss am') from dual;
--Today's date and the time right now (normal)
select to_char(sysdate,'Mon/dd/yyyy hh12:mi:ss am') from dual;
--Today's date and the time right now (military)
select to_char(sysdate,'Month/dd/yyyy hh24:mi:ss') from dual;
--Day of the week
select to_char(sysdate,'d') from dual;
--Day of the week
select to_char(sysdate,'Day') from dual;
--Day of the year
select to_char(sysdate,'ddd') from dual;
--Week of the year
select to_char(sysdate,'ww') from dual;
--Subtract 3 days from now
select to_char(sysdate-3,'Mon/dd/yyyy hh12:mi:ss am') from dual; 
--Subtract 3 hours from now
select to_char(sysdate-3/24,'Mon/dd/yyyy hh12:mi:ss am') from dual; 
--Subtract 3 minutes from now
select to_char(sysdate-3/(24*60),'Mon/dd/yyyy hh12:mi:ss am') from dual; 
--Number of days between two dates (might need to use round function, especially in ARGOS)
select sysdate - to_date('02-19-2009','mm-dd-yy') from dual;
--What is the last day of the month
select last_day(sysdate) from dual;
--Subtract 1 month
select add_months(sysdate,-1) from dual;
--What is the date next Tuesday
select next_day(sysdate,'Tuesday') from dual;
--How many months between to dates (might need to use round function, especially in ARGOS)
select months_between(sysdate,sysdate+250) from dual;
--Convert a string to a date
select to_date('2009/01/22','yyyy/mm/dd') from dual;
select to_date('09-01-22','yy-mm-dd') from dual;
select to_date('2009/Jan/22','yyyy/Mon/dd') from dual;
select to_date('2009Jan22','yyyyMondd') from dual;
--remove extra spaces
select to_char(sysdate,'dd/Month/yy') from dual;
select replace(to_char(sysdate,'dd/Month/yy'),' ') from dual;



--What is the highest test score a student has (create a new view owned by ATU_IN_HOUSE)
 CREATE VIEW ATU_IN_HOUSE.ATU_MAX_TEST_SCORES       AS
 SELECT spriden_pidm                                AS enttest_pidm    ,
    f_test_score_fnc(spriden_pidm,'A05','HIGH','N') AS ACT_Comp        ,
    f_test_score_fnc(spriden_pidm,'A01','HIGH','N') AS ACT_Engl        ,
    f_test_score_fnc(spriden_pidm,'A02','HIGH','N') AS ACT_Math        ,
    f_test_score_fnc(spriden_pidm,'A03','HIGH','N') AS ACT_Read        ,
    f_test_score_fnc(spriden_pidm,'A04','HIGH','N') AS ACT_Sci         ,
    f_test_score_fnc(spriden_pidm,'S01','HIGH','N') AS SAT_Crit_Read   ,
    f_test_score_fnc(spriden_pidm,'S02','HIGH','N') AS SAT_Math        ,
    f_test_score_fnc(spriden_pidm,'S07','HIGH','N') AS SAT_Writing     ,
    f_test_score_fnc(spriden_pidm,'C00','HIGH','N') AS COMPASS_Comp    ,
    f_test_score_fnc(spriden_pidm,'C01','HIGH','N') AS COMPASS_Pre_Alg ,
    f_test_score_fnc(spriden_pidm,'C02','HIGH','N') AS COMPASS_Alg     ,
    f_test_score_fnc(spriden_pidm,'C03','HIGH','N') AS COMPASS_Coll_Alg,
    f_test_score_fnc(spriden_pidm,'C07','HIGH','N') AS COMPASS_Read    ,
    f_test_score_fnc(spriden_pidm,'C30','HIGH','N') AS COMPASS_Writing
   FROM spriden
  WHERE spriden_change_ind IS NULL
AND spriden_entity_ind      = 'P'
    /*
    The test function can read the FIRST, LAST, LOW, HIGH, COMBINED, or COMPOSITE for each test code.
    Use the last input field 'N', unless you want to convert and old test code to a new test code in GTVSDAX - then use a 'Y'.
    */
    ;
/*
create or replace public synonym ATU_MAX_TEST_SCORES
for ATU_IN_HOUSE.ATU_MAX_TEST_SCORES;
*/

 SELECT floor(dbms_random.value(100001, 999999)) AS pidm, ACT_COMP, ACT_ENGL, ACT_MATH, ACT_READ, ACT_SCI, SAT_CRIT_READ, SAT_MATH,
    SAT_WRITING, COMPASS_COMP, COMPASS_PRE_ALG, COMPASS_ALG, COMPASS_COLL_ALG, COMPASS_READ, COMPASS_WRITING
   FROM ATU_MAX_TEST_SCORES 
  WHERE ACT_COMP IS NOT NULL ;



--Look at financial aid
select pidm, all_financial_aid, tuition_and_fee_charges, room_and_board_charges, 
    book_charges, completed_semester, (tuition_and_fee_charges + room_and_board_charges + book_charges) as total_charges,
    case when (all_financial_aid - (tuition_and_fee_charges + room_and_board_charges + book_charges)) > 0 then 
        (all_financial_aid - (tuition_and_fee_charges + room_and_board_charges + book_charges)) 
        else 0 end as refund
from
(select   floor(dbms_random.value(100001, 999999)) AS pidm,
                            (nvl((select sum(nvl(F.rpratrm_paid_amt,0)) from rpratrm F, rfrbase   
                            where F.rpratrm_term_code = '200870'   
                            and F.rpratrm_pidm = spriden_pidm   
                            and rfrbase_fund_code = F.rpratrm_fund_code   
                            and F.rpratrm_fund_code not in('FWS','NWS') 
                            group by F.rpratrm_pidm),0) +  
                                  nvl((select sum(G.tbraccd_amount) from tbraccd G 
                                  where G.tbraccd_term_code = '200870' 
                                  and G.tbraccd_detail_code in('PTP1','PTP8','Y011','Y021','T031','Y041','Y051','Y053','Y061','Y071','Z011','Z013','Z021','Z031','Z041','Z051','Z053','Z061','Z071','Z081','Z091') 
                                  and G.tbraccd_pidm = spriden_pidm),0)) as All_Financial_Aid,    
                            (nvl((select sum(H.tbraccd_amount) from tbraccd H  
                            where H.tbraccd_term_code = '200870'   
                            and H.tbraccd_pidm = spriden_pidm   
                            and substr(H.tbraccd_detail_code,1,1) in('T','F')   
                            group by H.tbraccd_pidm),0)) as Tuition_and_Fee_Charges,   
                    (nvl((select sum(H.tbraccd_amount) from tbraccd H  
                            where H.tbraccd_term_code = '200870'   
                            and H.tbraccd_pidm = spriden_pidm   
                            and substr(H.tbraccd_detail_code,1,1) in('M','H')   
                            group by H.tbraccd_pidm),0)) as Room_and_Board_Charges,  
                                                  ((select count (*) from sfrstcr I   
                                                  where I.sfrstcr_rsts_code in('RE','RW','AU')   
                                                  and I.sfrstcr_term_code = '200870'   
                                                  and I.sfrstcr_pidm = spriden_pidm)*100) as Book_Charges,     
                  (CASE WHEN (select count(*) from sfrstcr S  
                            where S.sfrstcr_term_code = '200870'  
                            and S.sfrstcr_rsts_code in('RE','RW')  
                            and S.sfrstcr_pidm = spriden_pidm) > 0 then 'Yes'  
                  ELSE 'No' END) as Completed_Semester,
                                decode((select distinct 'Y' from rcrapp2 
                                where rcrapp2_aidy_code = '0809' 
                                and rcrapp2_infc_code = 'ISIR' 
                                and rcrapp2_pell_pgi = 0 
                                and rcrapp2_pidm = spriden_pidm),'Y','Full PELL Eligible','Not Full PELL Eligible') as Pell_Status
                  
from sgbstdn A, spriden   
where spriden_pidm in (select distinct T.sfrstcr_pidm from sfrstcr T  
                      where T.sfrstcr_term_code = '200870')   
and spriden_pidm = A.sgbstdn_pidm   
and spriden_change_ind is null   
and A.sgbstdn_term_code_eff = (select max(B.sgbstdn_term_code_eff) from sgbstdn B   
                              where A.sgbstdn_pidm = B.sgbstdn_pidm   
                              and B.sgbstdn_term_code_eff <= 200870) )
where pell_status = 'Full PELL Eligible'
and completed_semester = 'Yes'
;