TEACH Grant-Eligible Queries

The d_teach_grant_select is the original query used to find all TEACH Grant-eligible records. It is designed to pull both entering and current students for the selected year and term. If your school follows the setup steps for identifying TEACH Grant-eligible programs and enters percentile data for student test scores, then this query identifies all qualifying records. However, if your school uses a slightly different criteria set, then you will need to modify this query to meet those requirements.

Since this query is meant to address the needs of many institutions, it was written as a union to best pull in both entering and current students and to accommodate several parameter choices. Depending upon your school's parameter choices or custom needs, Jenzabar may recommend that certain union statements be removed altogether in order to maximize process efficiency.

The queries are identified numerically and descriptively to help you understand each and provide you with some general guidance on potential edits.

· Union Query 1 identifies current students who have an eligible Major or Minor and earned a 3.25 CAREER GPA or higher, if Career GPA choice is set in FAM.

· Union Query 2 identifies current students who have an eligible Certification, if defined, and earned a 3.25 CAREER GPA or higher, if Career GPA choice is set in FAM.

· Union Query 3 identifies current students who have an eligible Major or Minor and earned a 3.25 LOCAL GPA or higher, if Local GPA choice is set in FAM.

· Union Query 4 identifies current students who have an eligible Certification, if defined, and earned a 3.25 LOCAL GPA or higher, if Local GPA choice is set in FAM.

· Union Query 5 identifies current students twho have an eligible Major or Minor and scored above the 75th percentile on a national norm test of either SAT, ACT, or GRE.

· Union Query 6 identifies current students who have an eligible Certification and scored above the 75th percentile on a national norm test of either SAT, ACT, or GRE.

If your school will not track any entering students, then Jenzabar recommends you remove these queries.

If your school will track only entering transfers, then add the necessary logic that identifies them.

· If your school will track only entering students that have attained specific stages or stage levels, then JOIN the STAGE_CONFIG table as follows and add the necessary logic to the Where clause:
JOIN STAGE_CONFIG ON CANDIDACY.STAGE = STAGE_CONFIG.STAGE

· The following Where clause example pulls all stages at either the Accepted or Post-Accept stage level:
Where STAGE_CONFIG.STAGE_LEVEL IN ('ACCEPT', 'PSTACC')

· Union Query 7 identifies entering students who have an eligible Major Program and earned a 3.25 GPA or higher in High School or College.

· Union Query 8 identifies entering students who have an eligible Minor Program and earned a 3.25 GPA or higher in High School or College.

· Union Query 9 identifies entering students who have an eligible Certification Program and earned a 3.25 GPA or higher in High School or College.

· Union Query 10 identifies entering students who have an eligible Major Program and scored above the 75th percentile on a national norm test of either SAT, ACT, or GRE.

· Union Query 11 identifies entering students who have an eligible Minor Program and scored above the 75th percentile on a national norm test of either SAT, ACT, or GRE.

· Union Query 12 identifies entering students who have an eligible Certification Program and scored above the 75th percentile on a national norm test of either SAT, ACT, or GRE.

UNION
SELECT	DISTINCT NameMaster.ID_NUM
FROM	NameMaster
JOIN	BIOGRAPH_MASTER ON NameMaster.ID_NUM = BIOGRAPH_MASTER.ID_NUM
JOIN	STUDENT_MASTER ON NameMaster.ID_NUM = STUDENT_MASTER.ID_NUM
JOIN	STUD_TERM_SUM_DIV ON NameMaster.ID_NUM = STUD_TERM_SUM_DIV.ID_NUM AND STUD_TERM_SUM_DIV.YR_CDE = :as_year_code AND STUD_TERM_SUM_DIV.TRM_CDE = :as_term_code
JOIN	DEGREE_HISTORY ON STUDENT_MASTER.ID_NUM = DEGREE_HISTORY.ID_NUM AND STUDENT_MASTER.CUR_STUD_DIV= DEGREE_HISTORY.DIV_CDE AND DEGREE_HISTORY.CUR_DEGREE = 'Y'
JOIN	MAJOR_MINOR_DEF ON (DEGREE_HISTORY.MAJOR_1 = MAJOR_MINOR_DEF.MAJOR_CDE OR DEGREE_HISTORY.MAJOR_2 = MAJOR_MINOR_DEF.MAJOR_CDE OR DEGREE_HISTORY.MAJOR_3 = MAJOR_MINOR_DEF.MAJOR_CDE OR DEGREE_HISTORY.MINOR_1 = MAJOR_MINOR_DEF.MAJOR_CDE OR DEGREE_HISTORY.MINOR_2 = MAJOR_MINOR_DEF.MAJOR_CDE OR DEGREE_HISTORY.MINOR_3 = MAJOR_MINOR_DEF.MAJOR_CDE)
CROSS JOIN	PF_CONFIG
WHERE	PF_CONFIG.GPA_CHOICE = 'C' AND STUD_TERM_SUM_DIV.CAREER_GPA >= 3.25 AND STUDENT_MASTER.MOST_RECNT_YR_ENR = :as_year_code AND (BIOGRAPH_MASTER.CITIZENSHIP_STS IN ('C','R')) AND MAJOR_MINOR_DEF.TEACH_ELIGIBLE = 'Y'

UNION
SELECT	DISTINCT NameMaster.ID_NUM
FROM	NameMaster
JOIN	BIOGRAPH_MASTER ON NameMaster.ID_NUM = BIOGRAPH_MASTER.ID_NUM
JOIN	STUDENT_MASTER ON NameMaster.ID_NUM = STUDENT_MASTER.ID_NUM
JOIN	STUD_TERM_SUM_DIV ON NameMaster.ID_NUM = STUD_TERM_SUM_DIV.ID_NUM AND STUD_TERM_SUM_DIV.YR_CDE = :as_year_code AND STUD_TERM_SUM_DIV.TRM_CDE = :as_term_code
JOIN	DEGREE_HISTORY ON STUDENT_MASTER.ID_NUM = DEGREE_HISTORY.ID_NUM AND STUDENT_MASTER.CUR_STUD_DIV= DEGREE_HISTORY.DIV_CDE AND DEGREE_HISTORY.CUR_DEGREE = 'Y'
JOIN	CERTIFICATION_DEF ON (DEGREE_HISTORY.CERTIFICATION_1 = CERTIFICATION_DEF.CERT_CDE OR DEGREE_HISTORY.CERTIFICATION_2 = CERTIFICATION_DEF.CERT_CDE OR DEGREE_HISTORY.CERTIFICATION_3 = CERTIFICATION_DEF.CERT_CDE )
CROSS JOIN	PF_CONFIG
WHERE	PF_CONFIG.GPA_CHOICE = 'C'AND STUD_TERM_SUM_DIV.CAREER_GPA >= 3.25 AND STUDENT_MASTER.MOST_RECNT_YR_ENR = :as_year_code AND (BIOGRAPH_MASTER.CITIZENSHIP_STS IN ('C','R')) AND CERTIFICATION_DEF.TEACH_ELIGIBLE = 'Y'

UNION
SELECT	DISTINCT NameMaster.ID_NUM
FROM	NameMaster
JOIN	BIOGRAPH_MASTER ON NameMaster.ID_NUM = BIOGRAPH_MASTER.ID_NUM
JOIN	STUDENT_MASTER ON NameMaster.ID_NUM = STUDENT_MASTER.ID_NUM
JOIN	STUD_TERM_SUM_DIV ON NameMaster.ID_NUM = STUD_TERM_SUM_DIV.ID_NUM AND STUD_TERM_SUM_DIV.YR_CDE = :as_year_code AND STUD_TERM_SUM_DIV.TRM_CDE = :as_term_code
JOIN	DEGREE_HISTORY ON STUDENT_MASTER.ID_NUM = DEGREE_HISTORY.ID_NUM AND STUDENT_MASTER.CUR_STUD_DIV= DEGREE_HISTORY.DIV_CDE AND DEGREE_HISTORY.CUR_DEGREE = 'Y'
JOIN	MAJOR_MINOR_DEF ON (DEGREE_HISTORY.MAJOR_1 = MAJOR_MINOR_DEF.MAJOR_CDE OR DEGREE_HISTORY.MAJOR_2 = MAJOR_MINOR_DEF.MAJOR_CDE OR DEGREE_HISTORY.MAJOR_3 = MAJOR_MINOR_DEF.MAJOR_CDE OR DEGREE_HISTORY.MINOR_1 = MAJOR_MINOR_DEF.MAJOR_CDE OR DEGREE_HISTORY.MINOR_2 = MAJOR_MINOR_DEF.MAJOR_CDE OR DEGREE_HISTORY.MINOR_3 = MAJOR_MINOR_DEF.MAJOR_CDE )
CROSS JOIN	PF_CONFIG
WHERE	PF_CONFIG.GPA_CHOICE = 'L' AND STUD_TERM_SUM_DIV.LOCAL_GPA >= 3.25 AND STUDENT_MASTER.MOST_RECNT_YR_ENR = :as_year_code AND (BIOGRAPH_MASTER.CITIZENSHIP_STS IN ('C','R')) AND MAJOR_MINOR_DEF.TEACH_ELIGIBLE = 'Y'

UNION
SELECT	DISTINCT NameMaster.ID_NUM
FROM	NameMaster
JOIN	BIOGRAPH_MASTER ON NameMaster.ID_NUM = BIOGRAPH_MASTER.ID_NUM
JOIN	STUDENT_MASTER ON NameMaster.ID_NUM = STUDENT_MASTER.ID_NUM
JOIN	STUD_TERM_SUM_DIV ON NameMaster.ID_NUM = STUD_TERM_SUM_DIV.ID_NUM AND STUD_TERM_SUM_DIV.YR_CDE = :as_year_code AND STUD_TERM_SUM_DIV.TRM_CDE = :as_term_code
JOIN	DEGREE_HISTORY ON STUDENT_MASTER.ID_NUM = DEGREE_HISTORY.ID_NUM AND STUDENT_MASTER.CUR_STUD_DIV= DEGREE_HISTORY.DIV_CDE AND DEGREE_HISTORY.CUR_DEGREE = 'Y'
JOIN	CERTIFICATION_DEF ON (DEGREE_HISTORY.CERTIFICATION_1 = CERTIFICATION_DEF.CERT_CDE OR DEGREE_HISTORY.CERTIFICATION_2 = CERTIFICATION_DEF.CERT_CDE OR DEGREE_HISTORY.CERTIFICATION_3 = CERTIFICATION_DEF.CERT_CDE )
CROSS JOIN	PF_CONFIG
WHERE	PF_CONFIG.GPA_CHOICE = 'C'AND STUD_TERM_SUM_DIV.LOCAL_GPA >= 3.25 AND STUDENT_MASTER.MOST_RECNT_YR_ENR = :as_year_code AND (BIOGRAPH_MASTER.CITIZENSHIP_STS IN ('C','R')) AND CERTIFICATION_DEF.TEACH_ELIGIBLE = 'Y'

UNION
SELECT	DISTINCT NameMaster.ID_NUM
FROM	NameMaster
JOIN	BIOGRAPH_MASTER ON NameMaster.ID_NUM = BIOGRAPH_MASTER.ID_NUM
JOIN	STUDENT_MASTER ON NameMaster.ID_NUM = STUDENT_MASTER.ID_NUM
JOIN	DEGREE_HISTORY ON STUDENT_MASTER.ID_NUM = DEGREE_HISTORY.ID_NUM AND STUDENT_MASTER.CUR_STUD_DIV= DEGREE_HISTORY.DIV_CDE AND DEGREE_HISTORY.CUR_DEGREE = 'Y'
JOIN	MAJOR_MINOR_DEF ON (DEGREE_HISTORY.MAJOR_1 = MAJOR_MINOR_DEF.MAJOR_CDE or DEGREE_HISTORY.MAJOR_2 = MAJOR_MINOR_DEF.MAJOR_CDE or DEGREE_HISTORY.MAJOR_3 = MAJOR_MINOR_DEF.MAJOR_CDE or DEGREE_HISTORY.MINOR_1 = MAJOR_MINOR_DEF.MAJOR_CDE or DEGREE_HISTORY.MINOR_2 = MAJOR_MINOR_DEF.MAJOR_CDE or DEGREE_HISTORY.MINOR_3 = MAJOR_MINOR_DEF.MAJOR_CDE)
JOIN	TEST_SCORES_DETAIL ON NameMaster.ID_NUM = TEST_SCORES_DETAIL.ID_NUM
JOIN	PF_TEST_ELEMENT_XREF ON TEST_SCORES_DETAIL.TST_CDE = PF_TEST_ELEMENT_XREF.TST_CDE AND TEST_SCORES_DETAIL.TST_ELEM = PF_TEST_ELEMENT_XREF.TST_ELEM
WHERE	STUDENT_MASTER.MOST_RECNT_YR_ENR = :as_year_code AND (BIOGRAPH_MASTER.CITIZENSHIP_STS IN ('C','R')) AND MAJOR_MINOR_DEF.TEACH_ELIGIBLE = 'Y' AND test_scores_detail.TST_PERCENTILE > '75' AND test_scores_detail.TST_PERCENTILE <= '99' AND ((pf_test_element_xref.FAM_TEST_CDE = 'SAT' and pf_test_element_xref.FAM_TEST_ELEM = 'CR') or (pf_test_element_xref.FAM_TEST_CDE = 'SAT' and pf_test_element_xref.FAM_TEST_ELEM = 'M') or (pf_test_element_xref.FAM_TEST_CDE = 'SAT' and pf_test_element_xref.FAM_TEST_ELEM = 'WR') or (pf_test_element_xref.FAM_TEST_CDE = 'ACT' and pf_test_element_xref.FAM_TEST_ELEM = 'E') or (pf_test_element_xref.FAM_TEST_CDE = 'ACT' and pf_test_element_xref.FAM_TEST_ELEM = 'M') or (pf_test_element_xref.FAM_TEST_CDE = 'ACT' and pf_test_element_xref.FAM_TEST_ELEM = 'R') or (pf_test_element_xref.FAM_TEST_CDE = 'ACT' and pf_test_element_xref.FAM_TEST_ELEM = 'S') or (pf_test_element_xref.FAM_TEST_CDE = 'ACT' and pf_test_element_xref.FAM_TEST_ELEM = 'WR') or (pf_test_element_xref.FAM_TEST_CDE = 'GRE' and pf_test_element_xref.FAM_TEST_ELEM = 'V') or (pf_test_element_xref.FAM_TEST_CDE = 'GRE' and pf_test_element_xref.FAM_TEST_ELEM = 'Q') or (pf_test_element_xref.FAM_TEST_CDE = 'GRE' and pf_test_element_xref.FAM_TEST_ELEM = 'AW'))