asked
query help please
Hi all, I have this query that returns information about people and the schools they have attended and the programs they are involved in. The thing is some people have attended multiple schools which can be identified by the acad_credentials table because the person will have multiple records in this table. I need to populate a second set of fields (acad_gpa, ug_inst, acad_degree, and acad_institution_id) if the person has a second school. how can i manipulate this query to do this (right now it fills the fields with one school) and I have the case statement to eliminate information of people who attend my school (institution_id 0000004) SELECT STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_LAST_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_FIRST_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_MIDDLE_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM, STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS, STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_START_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_END_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_ANT_CMPL_DATE, { fn CONCAT({ fn CONCAT(STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, '*') }, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL) } AS STUDENT_ACAD_LEVEL, STUDENT_ACAD_LEVELS_VIEW.STA_CLASS, CASE WHEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' THEN PERSON.LAST_NAME ELSE NULL END AS UG_Inst, CASE WHEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' THEN ACAD_CREDENTIALS.ACAD_DEGREE ELSE NULL END AS ACAD_DEGREE, CASE WHEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' THEN ACAD_CREDENTIALS.ACAD_GPA ELSE NULL END AS ACAD_GPA, CASE WHEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' THEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID ELSE NULL END AS ACAD_INSTITUTIONS_ID FROM STUDENT_ACAD_LEVELS_VIEW FULL OUTER JOIN STUDENT_ACAD_PROGRAMS_VIEW LEFT OUTER JOIN PERSON RIGHT OUTER JOIN ACAD_CREDENTIALS ON
PERSON.ID = ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID ON STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID = ACAD_CREDENTIALS.ACAD_PERSON_ID ON STUDENT_ACAD_LEVELS_VIEW.STA_STUDENT = STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID AND STUDENT_ACAD_LEVELS_VIEW.STA_ACAD_LEVEL = STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL WHERE (STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS = 'Active') ORDER BY STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID
Do you only want the 2nd school details? What about 3rd/4th? How do you decide (from the data) which is the 1st / 2nd or other school - are they dated or ordered in some way?
Note, The way you design your database is the key issue here.if your database is well normalize, a simple query will do the job. Please give the list of tables you have and their Fields.
Here is what I need the expected output to be: STUDENT_ID, STUDENT_LAST_NAME, STUDENT_FIRST_NAME, STUDENT_MIDDLE_NAME, STP_ACAD_LEVEL (there could be 2) so STP_ACAD_LEVEL2 (if there isnt one than null), STP_ACADEMIC_PROGRAM (also could be 2) so STP_ACADEMIC_PROGRAM2 (if there isnt one than null), STP_CURRENT_STATUS, STP_CURRENT_STATUS_DATE, STP_START_DATE (one for each acad program) so there could be STP_START_DATE2, STP_END_DATE, STP_ANT_CMPL_DATE (could be one for each program), STUDENT_ACAD_LEVELS_ID (concatenation of STUDENT_ID '*' and STP_ACAD_LEVEL), STA_CLASS, [UG_Inst, ACAD_DEGREE, ACAD_GPA, ACAD_CREDENTIALS_ID] <--- these last 4 fields may have 2 values I so UG_Inst1, UG_Inst2, etc. I do not need to report more than 2 schools. I will include the DDLS for my tables in my next comment. Thank you for any help!!
Ok I formatted this: Here is STUDENT_ACAD_PROGRAMS_VIEW SELECT
LEFT(stp.student_programs_id, 7) AS
STUDENT_ID,
Substring(stp.student_programs_id, 9, Len(stp.student_programs_id)) AS
STP_ACADEMIC_PROGRAM,
Isnull(stp.stpr_title, programs.acpg_title) AS
STP_PROGRAM_TITLE,
stp.stpr_catalog AS
STP_CATALOG,
stp.stpr_dept AS
STP_DEPT,
stp.stpr_division AS
STP_DIVISION,
stp.stpr_school AS
STP_SCHOOL,
stp.stpr_ant_cmpl_date AS
STP_ANT_CMPL_DATE,
progstatuscodes.val_external_representation AS
STP_CURRENT_STATUS,
stp_statuses.prog_status_date AS
STP_CURRENT_STATUS_DATE,
stp_dates.stpr_start_date AS
STP_START_DATE,
stp_dates.stpr_end_date AS
STP_END_DATE,
CASE
WHEN stpr_end_date IS NULL THEN 'Y'
ELSE NULL
END AS
STP_PROGRAM_ACTIVE,
class.cls_desc AS
STUDENT_CLASS_LEVEL,
prog_ls1.acpg_majors AS
STP_MAJOR1,
prog_ls2.acpg_majors AS
STP_MAJOR2,
stp_addnl_majors1.stpr_addnl_majors AS
STP_ADDNL_MAJOR1,
stp_addnl_majors2.stpr_addnl_majors AS
STP_ADDNL_MAJOR2,
stp_addnl_majors3.stpr_addnl_majors AS
STP_ADDNL_MAJOR3,
prog_ls1.acpg_minors AS
STP_MINOR1,
prog_ls2.acpg_minors AS
STP_MINOR2,
stp_addnl_minors1.stpr_minors AS
STP_ADDNL_MINOR1,
stp_addnl_minors2.stpr_minors AS
STP_ADDNL_MINOR2,
stp_addnl_minors3.stpr_minors AS
STP_ADDNL_MINOR3,
prog_ls1.acpg_specializations AS
STP_SPECIALIZATION1,
prog_ls2.acpg_specializations AS
STP_SPECIALIZATION2,
prog_ls3.acpg_specializations AS
STP_SPECIALIZATION3,
stp_addnl_specs1.stpr_specializations AS
STP_ADDNL_SPECIALIZATION1,
stp_addnl_specs2.stpr_specializations AS
STP_ADDNL_SPECIALIZATION2,
stp_addnl_specs3.stpr_specializations AS
STP_ADDNL_SPECIALIZATION3,
prog_ls1.acpg_ccds AS
STP_CCDS1,
prog_ls2.acpg_ccds AS
STP_CCDS2,
prog_ls3.acpg_ccds AS
STP_CCDS3,
stp_addnl_ccds1.stpr_ccds AS
STP_ADDNL_CCD1,
stp_addnl_ccds2.stpr_ccds AS
STP_ADDNL_CCD2,
stp_addnl_ccds3.stpr_ccds AS
STP_ADDNL_CCD3,
CASE
WHEN programs.acpg_degree IS NOT NULL THEN 'Y'
ELSE 'N'
END AS
STP_DEGREE_GRANTING_FLAG,
programs.acpg_degree AS
STP_DEGREE,
programs.acpg_allow_graduation_flag AS
STP_ALLOW_GRADUATION_FLAG,
programs.acpg_acad_level AS
STP_ACAD_LEVEL,
stp.stpr_location AS
STP_LOCATION,
stp.stpr_eval_date AS
STP_EVAL_DATE,
stp.stpr_eval_status AS
STP_EVAL_STATUS,
stp.stpr_eval_combined_cred AS
STP_EVAL_COMBINED_CREDITS,
stp.stpr_eval_combined_gpa AS
STP_EVAL_COMBINED_GPA,
stp_major_eval1.stpr_eval_majors AS
STP_EVAL_MAJOR1,
stp_major_eval1.stpr_eval_major_gpa AS
STP_EVAL_MAJOR1_GPA,
stp_major_eval1.stpr_eval_major_cred_tot AS
STP_EVAL_MAJOR1_CRED_TOTALS,
stp_major_eval2.stpr_eval_majors AS
STP_EVAL_MAJOR2,
stp_major_eval2.stpr_eval_major_gpa AS
STP_EVAL_MAJOR2_GPA,
stp_major_eval2.stpr_eval_major_cred_tot AS
STP_EVAL_MAJOR2_CRED_TOTALS,
stp_major_eval3.stpr_eval_majors AS
STP_EVAL_MAJOR3,
stp_major_eval3.stpr_eval_major_gpa AS
STP_EVAL_MAJOR3_GPA,
stp_major_eval3.stpr_eval_major_cred_tot AS
STP_EVAL_MAJOR3_CRED_TOTALS,
person.last_name AS
STUDENT_LAST_NAME,
person.first_name AS
STUDENT_FIRST_NAME,
person.middle_name AS
STUDENT_MIDDLE_NAME,
person.preferred_name AS
STUDENT_PREFERRED_NAME,
person.nickname AS
STUDENT_NICKNAME,
person.gender AS
STUDENT_GENDER,
person.birth_date AS
STUDENT_BIRTH_DATE,
dbo. Vw_s_calc_age(person.birth_date, Getdate()) AS
STUDENT_AGE,
person.citizenship AS
STUDENT_CITIZENSHIP,
CASE
WHEN is_fper_alien = 'Y'
OR is_ethnic_alien = 'Y'
OR is_races_alien = 'Y' THEN 'Non-Resident Alien'
WHEN is_hispanic = 'Y' THEN 'Hispanic/Latino'
WHEN race_count >= '2' THEN 'Two or More Races'
WHEN race_max = '1' THEN 'American Indian'
WHEN race_max = '2' THEN 'Asian'
WHEN race_max = '3' THEN 'Black or African American'
WHEN race_max = '4' THEN 'Hawaiian/Pacific Islander'
WHEN race_max = '5' THEN 'White'
ELSE 'Unknown'
END AS
IPEDS_RACE_ETHNIC_DESC,
PERSON_MAIN_ETHNIC_RACE_INFO.person_ethnic_1,
PERSON_MAIN_ETHNIC_RACE_INFO.person_ethnic_desc_1,
PERSON_MAIN_ETHNIC_RACE_INFO.person_ethnic_2,
PERSON_MAIN_ETHNIC_RACE_INFO.person_ethnic_desc_2,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_1,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_desc_1,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_2,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_desc_2,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_3,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_desc_3,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_4,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_desc_4,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_5,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_desc_5,
person.residence_state AS
STUDENT_RESIDENCE_STATE,
privacycodes.val_external_representation AS
STUDENT_PRIVACY_FLAG,
directorycodes.val_external_representation AS
STUDENT_DIRECTORY_FLAG,
email.person_email_types AS
STUDENT_EMAIL,
email.person_email_addresses AS
STUDENT_EMAIL_TYPE,
acad_levels.aclv_transcript_grouping AS
TRGR_GPA_TRANSCRIPT_GROUPING
FROM dbo.student_programs AS stp WITH (nolock)
LEFT OUTER JOIN dbo.student_acad_prog_status_view AS stp_statuses WITH (
nolock)
ON stp.student_programs_id =
stp_statuses.student_programs_id
LEFT OUTER JOIN dbo.stpr_dates AS stp_dates WITH (nolock)
ON stp.student_programs_id = stp_dates.student_programs_id
AND stp_dates.pos = 1
LEFT OUTER JOIN dbo.stpr_major_list AS stp_addnl_majors1 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_majors1.student_programs_id
AND stp_addnl_majors1.pos = 1
LEFT OUTER JOIN dbo.stpr_major_list AS stp_addnl_majors2 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_majors2.student_programs_id
AND stp_addnl_majors2.pos = 2
LEFT OUTER JOIN dbo.stpr_major_list AS stp_addnl_majors3 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_majors3.student_programs_id
AND stp_addnl_majors3.pos = 3
LEFT OUTER JOIN dbo.stpr_ccd_list AS stp_addnl_ccds1 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_ccds1.student_programs_id
AND stp_addnl_ccds1.pos = 1
LEFT OUTER JOIN dbo.stpr_ccd_list AS stp_addnl_ccds2 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_ccds2.student_programs_id
AND stp_addnl_ccds2.pos = 2
LEFT OUTER JOIN dbo.stpr_ccd_list AS stp_addnl_ccds3 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_ccds3.student_programs_id
AND stp_addnl_ccds3.pos = 3
LEFT OUTER JOIN dbo.stpr_minor_list AS stp_addnl_minors1 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_minors1.student_programs_id
AND stp_addnl_minors1.pos = 1
LEFT OUTER JOIN dbo.stpr_minor_list AS stp_addnl_minors2 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_minors2.student_programs_id
AND stp_addnl_minors2.pos = 2
LEFT OUTER JOIN dbo.stpr_minor_list AS stp_addnl_minors3 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_minors3.student_programs_id
AND stp_addnl_minors3.pos = 3
LEFT OUTER JOIN dbo.stpr_specialties AS stp_addnl_specs1 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_specs1.student_programs_id
AND stp_addnl_specs1.pos = 1
LEFT OUTER JOIN dbo.stpr_specialties AS stp_addnl_specs2 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_specs2.student_programs_id
AND stp_addnl_specs2.pos = 2
LEFT OUTER JOIN dbo.stpr_specialties AS stp_addnl_specs3 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_specs3.student_programs_id
AND stp_addnl_specs3.pos = 3
LEFT OUTER JOIN dbo.stpr_major_eval AS stp_major_eval1 WITH (nolock)
ON stp.student_programs_id =
stp_major_eval1.student_programs_id
AND stp_major_eval1.pos = 1
LEFT OUTER JOIN dbo.stpr_major_eval AS stp_major_eval2 WITH (nolock)
ON stp.student_programs_id =
stp_major_eval2.student_programs_id
AND stp_major_eval2.pos = 2
LEFT OUTER JOIN dbo.stpr_major_eval AS stp_major_eval3 WITH (nolock)
ON stp.student_programs_id =
stp_major_eval3.student_programs_id
AND stp_major_eval3.pos = 3
LEFT OUTER JOIN dbo.acad_programs_ls AS prog_ls1 WITH (nolock)
ON Substring(stp.student_programs_id, 9,
Len(stp.student_programs_id)) =
prog_ls1.acad_programs_id
AND prog_ls1.pos = 1
LEFT OUTER JOIN dbo.acad_programs_ls AS prog_ls2 WITH (nolock)
ON Substring(stp.student_programs_id, 9,
Len(stp.student_programs_id)) =
prog_ls2.acad_programs_id
AND prog_ls2.pos = 2
LEFT OUTER JOIN dbo.acad_programs_ls AS prog_ls3 WITH (nolock)
ON Substring(stp.student_programs_id, 9,
Len(stp.student_programs_id)) =
prog_ls3.acad_programs_id
AND prog_ls3.pos = 3
LEFT OUTER JOIN dbo.acad_programs AS programs WITH (nolock)
ON Substring(stp.student_programs_id, 9,
Len(stp.student_programs_id)) =
programs.acad_programs_id
LEFT OUTER JOIN dbo.student_acad_levels AS levels WITH (nolock)
ON LEFT(stp.student_programs_id, 7) + '*'
+ programs.acpg_acad_level =
levels.student_acad_levels_id
LEFT OUTER JOIN dbo.acad_levels AS acad_levels WITH (nolock)
ON programs.acpg_acad_level = acad_levels.acad_levels_id
LEFT OUTER JOIN dbo.classes AS class WITH (nolock)
ON class.classes_id = levels.sta_class
LEFT OUTER JOIN dbo.person AS person WITH (nolock)
ON LEFT(stp.student_programs_id, 7) = person.id
LEFT OUTER JOIN (SELECT pr_ls.id,
Count( pr_ls.id) AS
LS_REC_COUNT,
Max(CASE alien_vals.val_action_code_1
WHEN 'NRA' THEN 'Y'
ELSE 'N'
END) AS
IS_FPER_ALIEN,
Max(CASE ethnics_vals.val_action_code_1
WHEN 'NRA' THEN 'Y'
ELSE 'N'
END) AS
IS_ETHNIC_ALIEN,
Max(CASE races_vals.val_action_code_1
WHEN 'NRA' THEN 'Y'
ELSE 'N'
END) AS
IS_RACES_ALIEN,
Max(CASE ethnics_vals.val_action_code_1
WHEN 'H' THEN 'Y'
ELSE 'N'
END) AS
IS_HISPANIC,
Count(DISTINCT races_vals.val_action_code_1) AS
RACE_COUNT,
Max(races_vals.val_action_code_1) AS
RACE_MAX
,
Max(CASE pr_ls.pos
WHEN 1 THEN pr_ls.per_ethnics
ELSE NULL
END) AS
PERSON_ETHNIC_1,
Max(CASE pr_ls.pos
WHEN 1 THEN
ethnics_vals.val_external_representation
ELSE NULL
END) AS
PERSON_ETHNIC_DESC_1,
Max(CASE pr_ls.pos
WHEN 2 THEN pr_ls.per_ethnics
ELSE NULL
END) AS
PERSON_ETHNIC_2,
Max(CASE pr_ls.pos
WHEN 2 THEN
ethnics_vals.val_external_representation
ELSE NULL
END) AS
PERSON_ETHNIC_DESC_2,
Max(CASE pr_ls.pos
WHEN 1 THEN pr_ls.per_races
ELSE NULL
END) AS
PERSON_RACE_1,
Max(CASE pr_ls.pos
WHEN 1 THEN
races_vals.val_external_representation
ELSE NULL
END) AS
PERSON_RACE_DESC_1,
Max(CASE pr_ls.pos
WHEN 2 THEN pr_ls.per_races
ELSE NULL
END) AS
PERSON_RACE_2,
Max(CASE pr_ls.pos
WHEN 2 THEN
races_vals.val_external_representation
ELSE NULL
END) AS
PERSON_RACE_DESC_2,
Max(CASE pr_ls.pos
WHEN 3 THEN pr_ls.per_races
ELSE NULL
END) AS
PERSON_RACE_3,
Max(CASE pr_ls.pos
WHEN 3 THEN
races_vals.val_external_representation
ELSE NULL
END) AS
PERSON_RACE_DESC_3,
Max(CASE pr_ls.pos
WHEN 4 THEN pr_ls.per_races
ELSE NULL
END) AS
PERSON_RACE_4,
Max(CASE pr_ls.pos
WHEN 4 THEN
races_vals.val_external_representation
ELSE NULL
END) AS
PERSON_RACE_DESC_4,
Max(CASE pr_ls.pos
WHEN 5 THEN pr_ls.per_races
ELSE NULL
END) AS
PERSON_RACE_5,
Max(CASE pr_ls.pos
WHEN 5 THEN
races_vals.val_external_representation
ELSE NULL
END) AS
PERSON_RACE_DESC_5
FROM dbo.person_ls AS pr_ls WITH (nolock)
LEFT OUTER JOIN dbo.foreign_person AS fpr WITH (
nolock)
ON pr_ls.id = fpr.foreign_person_id
LEFT OUTER JOIN dbo.vals AS ethnics_vals WITH (
nolock)
ON pr_ls.per_ethnics =
ethnics_vals.val_internal_code
AND ethnics_vals.valcode_id =
'PERSON.ETHNICS'
LEFT OUTER JOIN dbo.vals AS races_vals WITH (
nolock)
ON pr_ls.per_races =
races_vals.val_internal_code
AND races_vals.valcode_id =
'PERSON.RACES'
LEFT OUTER JOIN dbo.vals AS alien_vals WITH (
nolock)
ON fpr.fper_alien_status =
alien_vals.val_internal_code
AND alien_vals.valcode_id =
'ALIEN.STATUSES'
GROUP BY pr_ls.id) AS PERSON_MAIN_ETHNIC_RACE_INFO
ON LEFT(stp.student_programs_id, 7) =
PERSON_MAIN_ETHNIC_RACE_INFO.id
LEFT OUTER JOIN dbo.ethnics AS ethnics WITH (nolock)
ON person.ethnic = ethnics.ethnics_id
LEFT OUTER JOIN dbo.vals AS privacycodes WITH (nolock)
ON privacycodes.valcode_id = 'PRIVACY.CODES'
AND privacycodes.val_minimum_input_string =
person.privacy_flag
LEFT OUTER JOIN dbo.vals AS directorycodes WITH (nolock)
ON directorycodes.valcode_id = 'DIRECTORY.CODES'
AND directorycodes.val_minimum_input_string =
person.directory_flag
LEFT OUTER JOIN dbo.vals AS progstatuscodes WITH (nolock)
ON progstatuscodes.valcode_id = 'STUDENT.PROGRAM.STATUSES'
AND progstatuscodes.val_minimum_input_string =
stp_statuses.prog_status
LEFT OUTER JOIN dbo.people_email AS email WITH (nolock)
ON LEFT(stp.student_programs_id, 7) = email.id
AND email.person_preferred_email = 'Y'
STUDENT_ID,
Substring(stp.student_programs_id, 9, Len(stp.student_programs_id)) AS
STP_ACADEMIC_PROGRAM,
Isnull(stp.stpr_title, programs.acpg_title) AS
STP_PROGRAM_TITLE,
stp.stpr_catalog AS
STP_CATALOG,
stp.stpr_dept AS
STP_DEPT,
stp.stpr_division AS
STP_DIVISION,
stp.stpr_school AS
STP_SCHOOL,
stp.stpr_ant_cmpl_date AS
STP_ANT_CMPL_DATE,
progstatuscodes.val_external_representation AS
STP_CURRENT_STATUS,
stp_statuses.prog_status_date AS
STP_CURRENT_STATUS_DATE,
stp_dates.stpr_start_date AS
STP_START_DATE,
stp_dates.stpr_end_date AS
STP_END_DATE,
CASE
WHEN stpr_end_date IS NULL THEN 'Y'
ELSE NULL
END AS
STP_PROGRAM_ACTIVE,
class.cls_desc AS
STUDENT_CLASS_LEVEL,
prog_ls1.acpg_majors AS
STP_MAJOR1,
prog_ls2.acpg_majors AS
STP_MAJOR2,
stp_addnl_majors1.stpr_addnl_majors AS
STP_ADDNL_MAJOR1,
stp_addnl_majors2.stpr_addnl_majors AS
STP_ADDNL_MAJOR2,
stp_addnl_majors3.stpr_addnl_majors AS
STP_ADDNL_MAJOR3,
prog_ls1.acpg_minors AS
STP_MINOR1,
prog_ls2.acpg_minors AS
STP_MINOR2,
stp_addnl_minors1.stpr_minors AS
STP_ADDNL_MINOR1,
stp_addnl_minors2.stpr_minors AS
STP_ADDNL_MINOR2,
stp_addnl_minors3.stpr_minors AS
STP_ADDNL_MINOR3,
prog_ls1.acpg_specializations AS
STP_SPECIALIZATION1,
prog_ls2.acpg_specializations AS
STP_SPECIALIZATION2,
prog_ls3.acpg_specializations AS
STP_SPECIALIZATION3,
stp_addnl_specs1.stpr_specializations AS
STP_ADDNL_SPECIALIZATION1,
stp_addnl_specs2.stpr_specializations AS
STP_ADDNL_SPECIALIZATION2,
stp_addnl_specs3.stpr_specializations AS
STP_ADDNL_SPECIALIZATION3,
prog_ls1.acpg_ccds AS
STP_CCDS1,
prog_ls2.acpg_ccds AS
STP_CCDS2,
prog_ls3.acpg_ccds AS
STP_CCDS3,
stp_addnl_ccds1.stpr_ccds AS
STP_ADDNL_CCD1,
stp_addnl_ccds2.stpr_ccds AS
STP_ADDNL_CCD2,
stp_addnl_ccds3.stpr_ccds AS
STP_ADDNL_CCD3,
CASE
WHEN programs.acpg_degree IS NOT NULL THEN 'Y'
ELSE 'N'
END AS
STP_DEGREE_GRANTING_FLAG,
programs.acpg_degree AS
STP_DEGREE,
programs.acpg_allow_graduation_flag AS
STP_ALLOW_GRADUATION_FLAG,
programs.acpg_acad_level AS
STP_ACAD_LEVEL,
stp.stpr_location AS
STP_LOCATION,
stp.stpr_eval_date AS
STP_EVAL_DATE,
stp.stpr_eval_status AS
STP_EVAL_STATUS,
stp.stpr_eval_combined_cred AS
STP_EVAL_COMBINED_CREDITS,
stp.stpr_eval_combined_gpa AS
STP_EVAL_COMBINED_GPA,
stp_major_eval1.stpr_eval_majors AS
STP_EVAL_MAJOR1,
stp_major_eval1.stpr_eval_major_gpa AS
STP_EVAL_MAJOR1_GPA,
stp_major_eval1.stpr_eval_major_cred_tot AS
STP_EVAL_MAJOR1_CRED_TOTALS,
stp_major_eval2.stpr_eval_majors AS
STP_EVAL_MAJOR2,
stp_major_eval2.stpr_eval_major_gpa AS
STP_EVAL_MAJOR2_GPA,
stp_major_eval2.stpr_eval_major_cred_tot AS
STP_EVAL_MAJOR2_CRED_TOTALS,
stp_major_eval3.stpr_eval_majors AS
STP_EVAL_MAJOR3,
stp_major_eval3.stpr_eval_major_gpa AS
STP_EVAL_MAJOR3_GPA,
stp_major_eval3.stpr_eval_major_cred_tot AS
STP_EVAL_MAJOR3_CRED_TOTALS,
person.last_name AS
STUDENT_LAST_NAME,
person.first_name AS
STUDENT_FIRST_NAME,
person.middle_name AS
STUDENT_MIDDLE_NAME,
person.preferred_name AS
STUDENT_PREFERRED_NAME,
person.nickname AS
STUDENT_NICKNAME,
person.gender AS
STUDENT_GENDER,
person.birth_date AS
STUDENT_BIRTH_DATE,
dbo. Vw_s_calc_age(person.birth_date, Getdate()) AS
STUDENT_AGE,
person.citizenship AS
STUDENT_CITIZENSHIP,
CASE
WHEN is_fper_alien = 'Y'
OR is_ethnic_alien = 'Y'
OR is_races_alien = 'Y' THEN 'Non-Resident Alien'
WHEN is_hispanic = 'Y' THEN 'Hispanic/Latino'
WHEN race_count >= '2' THEN 'Two or More Races'
WHEN race_max = '1' THEN 'American Indian'
WHEN race_max = '2' THEN 'Asian'
WHEN race_max = '3' THEN 'Black or African American'
WHEN race_max = '4' THEN 'Hawaiian/Pacific Islander'
WHEN race_max = '5' THEN 'White'
ELSE 'Unknown'
END AS
IPEDS_RACE_ETHNIC_DESC,
PERSON_MAIN_ETHNIC_RACE_INFO.person_ethnic_1,
PERSON_MAIN_ETHNIC_RACE_INFO.person_ethnic_desc_1,
PERSON_MAIN_ETHNIC_RACE_INFO.person_ethnic_2,
PERSON_MAIN_ETHNIC_RACE_INFO.person_ethnic_desc_2,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_1,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_desc_1,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_2,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_desc_2,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_3,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_desc_3,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_4,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_desc_4,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_5,
PERSON_MAIN_ETHNIC_RACE_INFO.person_race_desc_5,
person.residence_state AS
STUDENT_RESIDENCE_STATE,
privacycodes.val_external_representation AS
STUDENT_PRIVACY_FLAG,
directorycodes.val_external_representation AS
STUDENT_DIRECTORY_FLAG,
email.person_email_types AS
STUDENT_EMAIL,
email.person_email_addresses AS
STUDENT_EMAIL_TYPE,
acad_levels.aclv_transcript_grouping AS
TRGR_GPA_TRANSCRIPT_GROUPING
FROM dbo.student_programs AS stp WITH (nolock)
LEFT OUTER JOIN dbo.student_acad_prog_status_view AS stp_statuses WITH (
nolock)
ON stp.student_programs_id =
stp_statuses.student_programs_id
LEFT OUTER JOIN dbo.stpr_dates AS stp_dates WITH (nolock)
ON stp.student_programs_id = stp_dates.student_programs_id
AND stp_dates.pos = 1
LEFT OUTER JOIN dbo.stpr_major_list AS stp_addnl_majors1 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_majors1.student_programs_id
AND stp_addnl_majors1.pos = 1
LEFT OUTER JOIN dbo.stpr_major_list AS stp_addnl_majors2 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_majors2.student_programs_id
AND stp_addnl_majors2.pos = 2
LEFT OUTER JOIN dbo.stpr_major_list AS stp_addnl_majors3 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_majors3.student_programs_id
AND stp_addnl_majors3.pos = 3
LEFT OUTER JOIN dbo.stpr_ccd_list AS stp_addnl_ccds1 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_ccds1.student_programs_id
AND stp_addnl_ccds1.pos = 1
LEFT OUTER JOIN dbo.stpr_ccd_list AS stp_addnl_ccds2 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_ccds2.student_programs_id
AND stp_addnl_ccds2.pos = 2
LEFT OUTER JOIN dbo.stpr_ccd_list AS stp_addnl_ccds3 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_ccds3.student_programs_id
AND stp_addnl_ccds3.pos = 3
LEFT OUTER JOIN dbo.stpr_minor_list AS stp_addnl_minors1 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_minors1.student_programs_id
AND stp_addnl_minors1.pos = 1
LEFT OUTER JOIN dbo.stpr_minor_list AS stp_addnl_minors2 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_minors2.student_programs_id
AND stp_addnl_minors2.pos = 2
LEFT OUTER JOIN dbo.stpr_minor_list AS stp_addnl_minors3 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_minors3.student_programs_id
AND stp_addnl_minors3.pos = 3
LEFT OUTER JOIN dbo.stpr_specialties AS stp_addnl_specs1 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_specs1.student_programs_id
AND stp_addnl_specs1.pos = 1
LEFT OUTER JOIN dbo.stpr_specialties AS stp_addnl_specs2 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_specs2.student_programs_id
AND stp_addnl_specs2.pos = 2
LEFT OUTER JOIN dbo.stpr_specialties AS stp_addnl_specs3 WITH (nolock)
ON stp.student_programs_id =
stp_addnl_specs3.student_programs_id
AND stp_addnl_specs3.pos = 3
LEFT OUTER JOIN dbo.stpr_major_eval AS stp_major_eval1 WITH (nolock)
ON stp.student_programs_id =
stp_major_eval1.student_programs_id
AND stp_major_eval1.pos = 1
LEFT OUTER JOIN dbo.stpr_major_eval AS stp_major_eval2 WITH (nolock)
ON stp.student_programs_id =
stp_major_eval2.student_programs_id
AND stp_major_eval2.pos = 2
LEFT OUTER JOIN dbo.stpr_major_eval AS stp_major_eval3 WITH (nolock)
ON stp.student_programs_id =
stp_major_eval3.student_programs_id
AND stp_major_eval3.pos = 3
LEFT OUTER JOIN dbo.acad_programs_ls AS prog_ls1 WITH (nolock)
ON Substring(stp.student_programs_id, 9,
Len(stp.student_programs_id)) =
prog_ls1.acad_programs_id
AND prog_ls1.pos = 1
LEFT OUTER JOIN dbo.acad_programs_ls AS prog_ls2 WITH (nolock)
ON Substring(stp.student_programs_id, 9,
Len(stp.student_programs_id)) =
prog_ls2.acad_programs_id
AND prog_ls2.pos = 2
LEFT OUTER JOIN dbo.acad_programs_ls AS prog_ls3 WITH (nolock)
ON Substring(stp.student_programs_id, 9,
Len(stp.student_programs_id)) =
prog_ls3.acad_programs_id
AND prog_ls3.pos = 3
LEFT OUTER JOIN dbo.acad_programs AS programs WITH (nolock)
ON Substring(stp.student_programs_id, 9,
Len(stp.student_programs_id)) =
programs.acad_programs_id
LEFT OUTER JOIN dbo.student_acad_levels AS levels WITH (nolock)
ON LEFT(stp.student_programs_id, 7) + '*'
+ programs.acpg_acad_level =
levels.student_acad_levels_id
LEFT OUTER JOIN dbo.acad_levels AS acad_levels WITH (nolock)
ON programs.acpg_acad_level = acad_levels.acad_levels_id
LEFT OUTER JOIN dbo.classes AS class WITH (nolock)
ON class.classes_id = levels.sta_class
LEFT OUTER JOIN dbo.person AS person WITH (nolock)
ON LEFT(stp.student_programs_id, 7) = person.id
LEFT OUTER JOIN (SELECT pr_ls.id,
Count( pr_ls.id) AS
LS_REC_COUNT,
Max(CASE alien_vals.val_action_code_1
WHEN 'NRA' THEN 'Y'
ELSE 'N'
END) AS
IS_FPER_ALIEN,
Max(CASE ethnics_vals.val_action_code_1
WHEN 'NRA' THEN 'Y'
ELSE 'N'
END) AS
IS_ETHNIC_ALIEN,
Max(CASE races_vals.val_action_code_1
WHEN 'NRA' THEN 'Y'
ELSE 'N'
END) AS
IS_RACES_ALIEN,
Max(CASE ethnics_vals.val_action_code_1
WHEN 'H' THEN 'Y'
ELSE 'N'
END) AS
IS_HISPANIC,
Count(DISTINCT races_vals.val_action_code_1) AS
RACE_COUNT,
Max(races_vals.val_action_code_1) AS
RACE_MAX
,
Max(CASE pr_ls.pos
WHEN 1 THEN pr_ls.per_ethnics
ELSE NULL
END) AS
PERSON_ETHNIC_1,
Max(CASE pr_ls.pos
WHEN 1 THEN
ethnics_vals.val_external_representation
ELSE NULL
END) AS
PERSON_ETHNIC_DESC_1,
Max(CASE pr_ls.pos
WHEN 2 THEN pr_ls.per_ethnics
ELSE NULL
END) AS
PERSON_ETHNIC_2,
Max(CASE pr_ls.pos
WHEN 2 THEN
ethnics_vals.val_external_representation
ELSE NULL
END) AS
PERSON_ETHNIC_DESC_2,
Max(CASE pr_ls.pos
WHEN 1 THEN pr_ls.per_races
ELSE NULL
END) AS
PERSON_RACE_1,
Max(CASE pr_ls.pos
WHEN 1 THEN
races_vals.val_external_representation
ELSE NULL
END) AS
PERSON_RACE_DESC_1,
Max(CASE pr_ls.pos
WHEN 2 THEN pr_ls.per_races
ELSE NULL
END) AS
PERSON_RACE_2,
Max(CASE pr_ls.pos
WHEN 2 THEN
races_vals.val_external_representation
ELSE NULL
END) AS
PERSON_RACE_DESC_2,
Max(CASE pr_ls.pos
WHEN 3 THEN pr_ls.per_races
ELSE NULL
END) AS
PERSON_RACE_3,
Max(CASE pr_ls.pos
WHEN 3 THEN
races_vals.val_external_representation
ELSE NULL
END) AS
PERSON_RACE_DESC_3,
Max(CASE pr_ls.pos
WHEN 4 THEN pr_ls.per_races
ELSE NULL
END) AS
PERSON_RACE_4,
Max(CASE pr_ls.pos
WHEN 4 THEN
races_vals.val_external_representation
ELSE NULL
END) AS
PERSON_RACE_DESC_4,
Max(CASE pr_ls.pos
WHEN 5 THEN pr_ls.per_races
ELSE NULL
END) AS
PERSON_RACE_5,
Max(CASE pr_ls.pos
WHEN 5 THEN
races_vals.val_external_representation
ELSE NULL
END) AS
PERSON_RACE_DESC_5
FROM dbo.person_ls AS pr_ls WITH (nolock)
LEFT OUTER JOIN dbo.foreign_person AS fpr WITH (
nolock)
ON pr_ls.id = fpr.foreign_person_id
LEFT OUTER JOIN dbo.vals AS ethnics_vals WITH (
nolock)
ON pr_ls.per_ethnics =
ethnics_vals.val_internal_code
AND ethnics_vals.valcode_id =
'PERSON.ETHNICS'
LEFT OUTER JOIN dbo.vals AS races_vals WITH (
nolock)
ON pr_ls.per_races =
races_vals.val_internal_code
AND races_vals.valcode_id =
'PERSON.RACES'
LEFT OUTER JOIN dbo.vals AS alien_vals WITH (
nolock)
ON fpr.fper_alien_status =
alien_vals.val_internal_code
AND alien_vals.valcode_id =
'ALIEN.STATUSES'
GROUP BY pr_ls.id) AS PERSON_MAIN_ETHNIC_RACE_INFO
ON LEFT(stp.student_programs_id, 7) =
PERSON_MAIN_ETHNIC_RACE_INFO.id
LEFT OUTER JOIN dbo.ethnics AS ethnics WITH (nolock)
ON person.ethnic = ethnics.ethnics_id
LEFT OUTER JOIN dbo.vals AS privacycodes WITH (nolock)
ON privacycodes.valcode_id = 'PRIVACY.CODES'
AND privacycodes.val_minimum_input_string =
person.privacy_flag
LEFT OUTER JOIN dbo.vals AS directorycodes WITH (nolock)
ON directorycodes.valcode_id = 'DIRECTORY.CODES'
AND directorycodes.val_minimum_input_string =
person.directory_flag
LEFT OUTER JOIN dbo.vals AS progstatuscodes WITH (nolock)
ON progstatuscodes.valcode_id = 'STUDENT.PROGRAM.STATUSES'
AND progstatuscodes.val_minimum_input_string =
stp_statuses.prog_status
LEFT OUTER JOIN dbo.people_email AS email WITH (nolock)
ON LEFT(stp.student_programs_id, 7) = email.id
AND email.person_preferred_email = 'Y'
Here is STUDENT_ACAD_LEVELS_VIEW SELECT
LEFT(student_acad_levels_id,
Charindex('*', student_acad_levels_id + '*')
- 1)
AS STA_STUDENT,
LEFT( Substring(student_acad_levels_id,
Charindex('*', student_acad_levels_id +
'*')
+ 1, 999),
Charindex('*', Substring(student_acad_levels_id, Charindex('*',
student_acad_levels_id
+ '*') +
1, 999)
+ '*') - 1) AS STA_ACAD_LEVEL,
student_acad_levels_id,
sta_start_date,
sta_end_date,
sta_admit_status,
sta_enroll_status,
sta_class,
sta_class_rank,
sta_comments,
sta_start_term,
sta_student_load_intent,
sta_catalog,
sta_fed_cohort_group,
student_acad_levels_addopr,
student_acad_levels_adddate,
student_acad_levels_chgopr,
student_acad_levels_chgdate,
sta_user1,
sta_user2,
sta_user3,
sta_user4,
sta_user5,
sta_user6,
sta_user7,
sta_user8,
sta_user9,
sta_user10,
sta_user11,
sta_user12,
sta_user13,
sta_user14,
sta_user15,
sta_user16,
sta_user17,
sta_user18,
sta_user19,
sta_user20,
sta_user21,
sta_user22
FROM dbo.student_acad_levels WITH (nolock)
- 1)
AS STA_STUDENT,
LEFT( Substring(student_acad_levels_id,
Charindex('*', student_acad_levels_id +
'*')
+ 1, 999),
Charindex('*', Substring(student_acad_levels_id, Charindex('*',
student_acad_levels_id
+ '*') +
1, 999)
+ '*') - 1) AS STA_ACAD_LEVEL,
student_acad_levels_id,
sta_start_date,
sta_end_date,
sta_admit_status,
sta_enroll_status,
sta_class,
sta_class_rank,
sta_comments,
sta_start_term,
sta_student_load_intent,
sta_catalog,
sta_fed_cohort_group,
student_acad_levels_addopr,
student_acad_levels_adddate,
student_acad_levels_chgopr,
student_acad_levels_chgdate,
sta_user1,
sta_user2,
sta_user3,
sta_user4,
sta_user5,
sta_user6,
sta_user7,
sta_user8,
sta_user9,
sta_user10,
sta_user11,
sta_user12,
sta_user13,
sta_user14,
sta_user15,
sta_user16,
sta_user17,
sta_user18,
sta_user19,
sta_user20,
sta_user21,
sta_user22
FROM dbo.student_acad_levels WITH (nolock)
Here is PERSON: USE [coll18_production]
go
/****** Object: Table [dbo].[PERSON] Script Date: 06/28/2012 09:43:23 ******/
SET ansi_nulls OFF
go
SET quoted_identifier ON
go
SET ansi_padding ON
go
CREATE TABLE [dbo].[person]
(
[id] [VARCHAR](10) NOT NULL,
[last_name] [VARCHAR](57) NULL,
[source] [VARCHAR](10) NULL,
[first_name] [VARCHAR](30) NULL,
[middle_name] [VARCHAR](30) NULL,
[prefix] [VARCHAR](25) NULL,
[preferred_address] [VARCHAR](10) NULL,
[joint_person] [VARCHAR](10) NULL,
[ssn] [VARCHAR](12) NULL,
[person_status] [VARCHAR](10) NULL,
[gender] [VARCHAR](1) NULL,
[marital_status] [VARCHAR](10) NULL,
[birth_date] [DATETIME] NULL,
[preferred_name] [VARCHAR](300) NULL,
[person_add_operator] [VARCHAR](20) NULL,
[spouse] [VARCHAR](10) NULL,
[nickname] [VARCHAR](15) NULL,
[ethnic] [VARCHAR](5) NULL,
[person_change_operator] [VARCHAR](20) NULL,
[person_change_date] [DATETIME] NULL,
[person_add_date] [DATETIME] NULL,
[suffix] [VARCHAR](25) NULL,
[person_native_language] [VARCHAR](10) NULL,
[anonymous] [VARCHAR](1) NULL,
[deceased_date] [DATETIME] NULL,
[birth_name_last] [VARCHAR](57) NULL,
[birth_name_first] [VARCHAR](30) NULL,
[birth_name_middle] [VARCHAR](30) NULL,
[preferred_residence] [VARCHAR](10) NULL,
[person_ben_id] [VARCHAR](10) NULL,
[income_level] [VARCHAR](20) NULL,
[pref_bus_address] [VARCHAR](10) NULL,
[person_country_entry_date] [DATETIME] NULL,
[premium_preference] [VARCHAR](10) NULL,
[person_corp_indicator] [VARCHAR](1) NULL,
[residence_country] [VARCHAR](8) NULL,
[box] [VARCHAR](10) NULL,
[person_donor_type] [VARCHAR](10) NULL,
[person_group_id] [VARCHAR](10) NULL,
[preferred_listing] [VARCHAR](60) NULL,
[obituary] [VARCHAR](10) NULL,
[memorial_type] [VARCHAR](10) NULL,
[person_origin_date] [DATETIME] NULL,
[person_origin_code] [VARCHAR](10) NULL,
[person_user1] [VARCHAR](10) NULL,
[person_user2] [VARCHAR](10) NULL,
[person_user3] [VARCHAR](10) NULL,
[person_user4] [VARCHAR](10) NULL,
[person_user5] [VARCHAR](10) NULL,
[person_user_changed_by] [VARCHAR](10) NULL,
[person_user_changed_date] [DATETIME] NULL,
[person_user6] [VARCHAR](10) NULL,
[person_user7] [VARCHAR](10) NULL,
[person_user8] [VARCHAR](10) NULL,
[person_user9] [VARCHAR](10) NULL,
[person_user10] [VARCHAR](10) NULL,
[person_merged_to_id] [VARCHAR](10) NULL,
[annuity_address] [VARCHAR](10) NULL,
[person_birth_place] [VARCHAR](30) NULL,
[denomination] [VARCHAR](2) NULL,
[rfa_segment] [VARCHAR](10) NULL,
[person_vip] [VARCHAR](4) NULL,
[political_party] [VARCHAR](4) NULL,
[visa_issued_date] [DATETIME] NULL,
[occupation] [VARCHAR](6) NULL,
[person_override_salutation] [VARCHAR](30) NULL,
[person_family_size] [VARCHAR](10) NULL,
[guardians] [VARCHAR](10) NULL,
[person_ovrl_emp_stat] [VARCHAR](10) NULL,
[participant_type] [VARCHAR](10) NULL,
[residence_county] [VARCHAR](7) NULL,
[residence_state] [VARCHAR](2) NULL,
[visa_type] [VARCHAR](20) NULL,
[visa_exp_date] [DATETIME] NULL,
[alien_id] [VARCHAR](12) NULL,
[alien_flag] [VARCHAR](10) NULL,
[selective_service_flag] [VARCHAR](3) NULL,
[selective_service_number] [VARCHAR](12) NULL,
[citizenship] [VARCHAR](8) NULL,
[emer_contact_name] [VARCHAR](30) NULL,
[emer_contact_phone] [VARCHAR](20) NULL,
[directory_flag] [VARCHAR](10) NULL,
[privacy_flag] [VARCHAR](10) NULL,
[immigration_status] [VARCHAR](10) NULL,
[pref_employment] [VARCHAR](10) NULL,
[person_primary_language] [VARCHAR](20) NULL,
[aars] [VARCHAR](10) NULL,
[driver_license_no] [VARCHAR](15) NULL,
[driver_license_state] [VARCHAR](2) NULL,
[person_home_lang_sch_no_yrs] [DECIMAL](2, 0) NULL,
[person_home_lang_sch_country] [VARCHAR](8) NULL,
[person_achievements] [TEXT] NULL,
[person_website_address] [VARCHAR](50) NULL,
[person_campus_orgs_id] [VARCHAR](5) NULL,
CONSTRAINT [PK_PERSON] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (pad_index =
OFF, statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks =
on, allow_page_locks = on, FILLFACTOR = 90) ON [IDX]
)
ON [IDX]
textimage_on [PRIMARY]
go
SET ansi_padding ON
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_AARS_PERSON] FOREIGN KEY([aars])
REFERENCES [dbo].[person] ([id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_AARS_PERSON]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_DRIVER_LICENSE_STATE_STATES] FOREIGN
KEY([driver_license_state]) REFERENCES [dbo].[states] ([states_id]) NOT FOR
REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_DRIVER_LICENSE_STATE_STATES]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_ETHNIC_ETHNICS] FOREIGN KEY([ethnic])
REFERENCES [dbo].[ethnics] ([ethnics_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_ETHNIC_ETHNICS]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_ID_CORP_FOUNDS] FOREIGN KEY([id])
REFERENCES [dbo].[corp_founds] ([corp_founds_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_ID_CORP_FOUNDS]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_ID_SHOPPING_CART] FOREIGN KEY([id])
REFERENCES [dbo].[shopping_cart] ([shop_person_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_ID_SHOPPING_CART]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_OBITUARY_CLIPPING] FOREIGN KEY(
[obituary]) REFERENCES [dbo].[clipping] ([clipping_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_OBITUARY_CLIPPING]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_PERSON_HOME_LANG_SCH_COUNTRY_COUNTRIES]
FOREIGN KEY([person_home_lang_sch_country]) REFERENCES [dbo].[countries] (
[countries_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_PERSON_HOME_LANG_SCH_COUNTRY_COUNTRIES]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_PREFERRED_ADDRESS_ADDRESS] FOREIGN KEY(
[preferred_address]) REFERENCES [dbo].[address] ([address_id]) NOT FOR
REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_PREFERRED_ADDRESS_ADDRESS]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_PREFERRED_RESIDENCE_ADDRESS] FOREIGN
KEY([preferred_residence]) REFERENCES [dbo].[address] ([address_id]) NOT FOR
REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_PREFERRED_RESIDENCE_ADDRESS]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_RESIDENCE_COUNTRY_COUNTRIES] FOREIGN
KEY([residence_country]) REFERENCES [dbo].[countries] ([countries_id]) NOT FOR
REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_RESIDENCE_COUNTRY_COUNTRIES]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_RESIDENCE_COUNTY_COUNTIES] FOREIGN KEY(
[residence_county]) REFERENCES [dbo].[counties] ([counties_id]) NOT FOR
REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_RESIDENCE_COUNTY_COUNTIES]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_RESIDENCE_STATE_STATES] FOREIGN KEY(
[residence_state]) REFERENCES [dbo].[states] ([states_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_RESIDENCE_STATE_STATES]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_SPOUSE_PERSON] FOREIGN KEY([spouse])
REFERENCES [dbo].[person] ([id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_SPOUSE_PERSON]
go
go
/****** Object: Table [dbo].[PERSON] Script Date: 06/28/2012 09:43:23 ******/
SET ansi_nulls OFF
go
SET quoted_identifier ON
go
SET ansi_padding ON
go
CREATE TABLE [dbo].[person]
(
[id] [VARCHAR](10) NOT NULL,
[last_name] [VARCHAR](57) NULL,
[source] [VARCHAR](10) NULL,
[first_name] [VARCHAR](30) NULL,
[middle_name] [VARCHAR](30) NULL,
[prefix] [VARCHAR](25) NULL,
[preferred_address] [VARCHAR](10) NULL,
[joint_person] [VARCHAR](10) NULL,
[ssn] [VARCHAR](12) NULL,
[person_status] [VARCHAR](10) NULL,
[gender] [VARCHAR](1) NULL,
[marital_status] [VARCHAR](10) NULL,
[birth_date] [DATETIME] NULL,
[preferred_name] [VARCHAR](300) NULL,
[person_add_operator] [VARCHAR](20) NULL,
[spouse] [VARCHAR](10) NULL,
[nickname] [VARCHAR](15) NULL,
[ethnic] [VARCHAR](5) NULL,
[person_change_operator] [VARCHAR](20) NULL,
[person_change_date] [DATETIME] NULL,
[person_add_date] [DATETIME] NULL,
[suffix] [VARCHAR](25) NULL,
[person_native_language] [VARCHAR](10) NULL,
[anonymous] [VARCHAR](1) NULL,
[deceased_date] [DATETIME] NULL,
[birth_name_last] [VARCHAR](57) NULL,
[birth_name_first] [VARCHAR](30) NULL,
[birth_name_middle] [VARCHAR](30) NULL,
[preferred_residence] [VARCHAR](10) NULL,
[person_ben_id] [VARCHAR](10) NULL,
[income_level] [VARCHAR](20) NULL,
[pref_bus_address] [VARCHAR](10) NULL,
[person_country_entry_date] [DATETIME] NULL,
[premium_preference] [VARCHAR](10) NULL,
[person_corp_indicator] [VARCHAR](1) NULL,
[residence_country] [VARCHAR](8) NULL,
[box] [VARCHAR](10) NULL,
[person_donor_type] [VARCHAR](10) NULL,
[person_group_id] [VARCHAR](10) NULL,
[preferred_listing] [VARCHAR](60) NULL,
[obituary] [VARCHAR](10) NULL,
[memorial_type] [VARCHAR](10) NULL,
[person_origin_date] [DATETIME] NULL,
[person_origin_code] [VARCHAR](10) NULL,
[person_user1] [VARCHAR](10) NULL,
[person_user2] [VARCHAR](10) NULL,
[person_user3] [VARCHAR](10) NULL,
[person_user4] [VARCHAR](10) NULL,
[person_user5] [VARCHAR](10) NULL,
[person_user_changed_by] [VARCHAR](10) NULL,
[person_user_changed_date] [DATETIME] NULL,
[person_user6] [VARCHAR](10) NULL,
[person_user7] [VARCHAR](10) NULL,
[person_user8] [VARCHAR](10) NULL,
[person_user9] [VARCHAR](10) NULL,
[person_user10] [VARCHAR](10) NULL,
[person_merged_to_id] [VARCHAR](10) NULL,
[annuity_address] [VARCHAR](10) NULL,
[person_birth_place] [VARCHAR](30) NULL,
[denomination] [VARCHAR](2) NULL,
[rfa_segment] [VARCHAR](10) NULL,
[person_vip] [VARCHAR](4) NULL,
[political_party] [VARCHAR](4) NULL,
[visa_issued_date] [DATETIME] NULL,
[occupation] [VARCHAR](6) NULL,
[person_override_salutation] [VARCHAR](30) NULL,
[person_family_size] [VARCHAR](10) NULL,
[guardians] [VARCHAR](10) NULL,
[person_ovrl_emp_stat] [VARCHAR](10) NULL,
[participant_type] [VARCHAR](10) NULL,
[residence_county] [VARCHAR](7) NULL,
[residence_state] [VARCHAR](2) NULL,
[visa_type] [VARCHAR](20) NULL,
[visa_exp_date] [DATETIME] NULL,
[alien_id] [VARCHAR](12) NULL,
[alien_flag] [VARCHAR](10) NULL,
[selective_service_flag] [VARCHAR](3) NULL,
[selective_service_number] [VARCHAR](12) NULL,
[citizenship] [VARCHAR](8) NULL,
[emer_contact_name] [VARCHAR](30) NULL,
[emer_contact_phone] [VARCHAR](20) NULL,
[directory_flag] [VARCHAR](10) NULL,
[privacy_flag] [VARCHAR](10) NULL,
[immigration_status] [VARCHAR](10) NULL,
[pref_employment] [VARCHAR](10) NULL,
[person_primary_language] [VARCHAR](20) NULL,
[aars] [VARCHAR](10) NULL,
[driver_license_no] [VARCHAR](15) NULL,
[driver_license_state] [VARCHAR](2) NULL,
[person_home_lang_sch_no_yrs] [DECIMAL](2, 0) NULL,
[person_home_lang_sch_country] [VARCHAR](8) NULL,
[person_achievements] [TEXT] NULL,
[person_website_address] [VARCHAR](50) NULL,
[person_campus_orgs_id] [VARCHAR](5) NULL,
CONSTRAINT [PK_PERSON] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (pad_index =
OFF, statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks =
on, allow_page_locks = on, FILLFACTOR = 90) ON [IDX]
)
ON [IDX]
textimage_on [PRIMARY]
go
SET ansi_padding ON
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_AARS_PERSON] FOREIGN KEY([aars])
REFERENCES [dbo].[person] ([id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_AARS_PERSON]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_DRIVER_LICENSE_STATE_STATES] FOREIGN
KEY([driver_license_state]) REFERENCES [dbo].[states] ([states_id]) NOT FOR
REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_DRIVER_LICENSE_STATE_STATES]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_ETHNIC_ETHNICS] FOREIGN KEY([ethnic])
REFERENCES [dbo].[ethnics] ([ethnics_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_ETHNIC_ETHNICS]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_ID_CORP_FOUNDS] FOREIGN KEY([id])
REFERENCES [dbo].[corp_founds] ([corp_founds_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_ID_CORP_FOUNDS]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_ID_SHOPPING_CART] FOREIGN KEY([id])
REFERENCES [dbo].[shopping_cart] ([shop_person_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_ID_SHOPPING_CART]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_OBITUARY_CLIPPING] FOREIGN KEY(
[obituary]) REFERENCES [dbo].[clipping] ([clipping_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_OBITUARY_CLIPPING]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_PERSON_HOME_LANG_SCH_COUNTRY_COUNTRIES]
FOREIGN KEY([person_home_lang_sch_country]) REFERENCES [dbo].[countries] (
[countries_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_PERSON_HOME_LANG_SCH_COUNTRY_COUNTRIES]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_PREFERRED_ADDRESS_ADDRESS] FOREIGN KEY(
[preferred_address]) REFERENCES [dbo].[address] ([address_id]) NOT FOR
REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_PREFERRED_ADDRESS_ADDRESS]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_PREFERRED_RESIDENCE_ADDRESS] FOREIGN
KEY([preferred_residence]) REFERENCES [dbo].[address] ([address_id]) NOT FOR
REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_PREFERRED_RESIDENCE_ADDRESS]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_RESIDENCE_COUNTRY_COUNTRIES] FOREIGN
KEY([residence_country]) REFERENCES [dbo].[countries] ([countries_id]) NOT FOR
REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_RESIDENCE_COUNTRY_COUNTRIES]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_RESIDENCE_COUNTY_COUNTIES] FOREIGN KEY(
[residence_county]) REFERENCES [dbo].[counties] ([counties_id]) NOT FOR
REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_RESIDENCE_COUNTY_COUNTIES]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_RESIDENCE_STATE_STATES] FOREIGN KEY(
[residence_state]) REFERENCES [dbo].[states] ([states_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_RESIDENCE_STATE_STATES]
go
ALTER TABLE [dbo].[person]
WITH NOCHECK ADD CONSTRAINT [FK_PERSON_SPOUSE_PERSON] FOREIGN KEY([spouse])
REFERENCES [dbo].[person] ([id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[person]
NOCHECK CONSTRAINT [FK_PERSON_SPOUSE_PERSON]
go
And here is ACAD_CREDENTIALS: USE [coll18_production]
go
/****** Object: Table [dbo].[ACAD_CREDENTIALS] Script Date: 06/28/2012 09:44:25 ******/
SET ansi_nulls OFF
go
SET quoted_identifier ON
go
SET ansi_padding ON
go
CREATE TABLE [dbo].[acad_credentials]
(
[acad_credentials_id] [VARCHAR](10) NOT NULL,
[acad_person_id] [VARCHAR](10) NULL,
[acad_institutions_id] [VARCHAR](10) NULL,
[acad_start_date] [DATETIME] NULL,
[acad_end_date] [DATETIME] NULL,
[acad_degree] [VARCHAR](5) NULL,
[acad_degree_date] [DATETIME] NULL,
[acad_division] [VARCHAR](5) NULL,
[acad_location] [VARCHAR](5) NULL,
[acad_acad_level] [VARCHAR](5) NULL,
[acad_no_years] [DECIMAL](2, 0) NULL,
[acad_commencement_date] [DATETIME] NULL,
[acad_diploma_name] [VARCHAR](40) NULL,
[acad_gpa] [DECIMAL](6, 3) NULL,
[acad_gpa1] [DECIMAL](6, 3) NULL,
[acad_gpa2] [DECIMAL](6, 3) NULL,
[acad_rank_percent] [DECIMAL](5, 2) NULL,
[acad_rank_numerator] [DECIMAL](7, 0) NULL,
[acad_rank_denominator] [DECIMAL](7, 0) NULL,
[acad_thesis] [TEXT] NULL,
[acad_comments] [TEXT] NULL,
[acad_credentials_addopr] [VARCHAR](20) NULL,
[acad_credentials_adddate] [DATETIME] NULL,
[acad_credentials_chgopr] [VARCHAR](20) NULL,
[acad_credentials_chgdate] [DATETIME] NULL,
[acad_transcript_city] [VARCHAR](30) NULL,
[acad_transcript_state] [VARCHAR](2) NULL,
[acad_transcript_zip] [VARCHAR](10) NULL,
[acad_transcript_country] [VARCHAR](8) NULL,
[acad_commencement_site] [VARCHAR](10) NULL,
[acad_user1] [VARCHAR](10) NULL,
[acad_user2] [VARCHAR](10) NULL,
[acad_user3] [VARCHAR](10) NULL,
[acad_user4] [VARCHAR](10) NULL,
[acad_user5] [VARCHAR](10) NULL,
[acad_user6] [VARCHAR](10) NULL,
[acad_user7] [VARCHAR](10) NULL,
[acad_user8] [VARCHAR](10) NULL,
[acad_user9] [VARCHAR](10) NULL,
[acad_user10] [VARCHAR](10) NULL,
[acad_acad_program] [VARCHAR](20) NULL,
[acad_cert_type] [VARCHAR](10) NULL,
[acad_cert_status] [VARCHAR](10) NULL,
[acad_cert_employable_by_code] [VARCHAR](10) NULL,
[acad_cert_staff_request_flag] [VARCHAR](1) NULL,
[acad_term] [VARCHAR](7) NULL,
[acad_cert_part_time_ind] [VARCHAR](10) NULL,
[acad_cert_part_time_ovr] [VARCHAR](1) NULL,
[acad_renewal_ind] [VARCHAR](1) NULL,
CONSTRAINT [PK_ACAD_CREDENTIALS] PRIMARY KEY CLUSTERED (
[acad_credentials_id] ASC )WITH (pad_index = OFF, statistics_norecompute =
OFF, ignore_dup_key = OFF, allow_row_locks = on, allow_page_locks = on,
FILLFACTOR = 90) ON [IDX]
)
ON [IDX]
textimage_on [PRIMARY]
go
SET ansi_padding ON
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT
[FK_ACAD_CREDENTIALS_ACAD_ACAD_LEVEL_OTHER_ACAD_LEVELS] FOREIGN KEY(
[acad_acad_level]) REFERENCES [dbo].[other_acad_levels] (
[other_acad_levels_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_ACAD_LEVEL_OTHER_ACAD_LEVELS]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_DEGREE_OTHER_DEGREES]
FOREIGN KEY([acad_degree]) REFERENCES [dbo].[other_degrees] (
[other_degrees_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_DEGREE_OTHER_DEGREES]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT
[FK_ACAD_CREDENTIALS_ACAD_DIVISION_OTHER_DIVISIONS] FOREIGN KEY(
[acad_division]) REFERENCES [dbo].[other_divisions] ([other_divisions_id]) NOT
FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_DIVISION_OTHER_DIVISIONS]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT
[FK_ACAD_CREDENTIALS_ACAD_INSTITUTIONS_ID_INSTITUTIONS] FOREIGN KEY(
[acad_institutions_id]) REFERENCES [dbo].[institutions] ([institutions_id])
NOT FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_INSTITUTIONS_ID_INSTITUTIONS]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT
[FK_ACAD_CREDENTIALS_ACAD_LOCATION_OTHER_LOCATIONS] FOREIGN KEY(
[acad_location]) REFERENCES [dbo].[other_locations] ([other_locations_id]) NOT
FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_LOCATION_OTHER_LOCATIONS]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_PERSON_ID_PERSON]
FOREIGN KEY([acad_person_id]) REFERENCES [dbo].[person] ([id]) NOT FOR
REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_PERSON_ID_PERSON]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT
[FK_ACAD_CREDENTIALS_ACAD_TRANSCRIPT_COUNTRY_COUNTRIES] FOREIGN KEY(
[acad_transcript_country]) REFERENCES [dbo].[countries] ([countries_id]) NOT
FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_TRANSCRIPT_COUNTRY_COUNTRIES]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_TRANSCRIPT_STATE_STATES]
FOREIGN KEY([acad_transcript_state]) REFERENCES [dbo].[states] ([states_id])
NOT FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_TRANSCRIPT_STATE_STATES]
go
go
/****** Object: Table [dbo].[ACAD_CREDENTIALS] Script Date: 06/28/2012 09:44:25 ******/
SET ansi_nulls OFF
go
SET quoted_identifier ON
go
SET ansi_padding ON
go
CREATE TABLE [dbo].[acad_credentials]
(
[acad_credentials_id] [VARCHAR](10) NOT NULL,
[acad_person_id] [VARCHAR](10) NULL,
[acad_institutions_id] [VARCHAR](10) NULL,
[acad_start_date] [DATETIME] NULL,
[acad_end_date] [DATETIME] NULL,
[acad_degree] [VARCHAR](5) NULL,
[acad_degree_date] [DATETIME] NULL,
[acad_division] [VARCHAR](5) NULL,
[acad_location] [VARCHAR](5) NULL,
[acad_acad_level] [VARCHAR](5) NULL,
[acad_no_years] [DECIMAL](2, 0) NULL,
[acad_commencement_date] [DATETIME] NULL,
[acad_diploma_name] [VARCHAR](40) NULL,
[acad_gpa] [DECIMAL](6, 3) NULL,
[acad_gpa1] [DECIMAL](6, 3) NULL,
[acad_gpa2] [DECIMAL](6, 3) NULL,
[acad_rank_percent] [DECIMAL](5, 2) NULL,
[acad_rank_numerator] [DECIMAL](7, 0) NULL,
[acad_rank_denominator] [DECIMAL](7, 0) NULL,
[acad_thesis] [TEXT] NULL,
[acad_comments] [TEXT] NULL,
[acad_credentials_addopr] [VARCHAR](20) NULL,
[acad_credentials_adddate] [DATETIME] NULL,
[acad_credentials_chgopr] [VARCHAR](20) NULL,
[acad_credentials_chgdate] [DATETIME] NULL,
[acad_transcript_city] [VARCHAR](30) NULL,
[acad_transcript_state] [VARCHAR](2) NULL,
[acad_transcript_zip] [VARCHAR](10) NULL,
[acad_transcript_country] [VARCHAR](8) NULL,
[acad_commencement_site] [VARCHAR](10) NULL,
[acad_user1] [VARCHAR](10) NULL,
[acad_user2] [VARCHAR](10) NULL,
[acad_user3] [VARCHAR](10) NULL,
[acad_user4] [VARCHAR](10) NULL,
[acad_user5] [VARCHAR](10) NULL,
[acad_user6] [VARCHAR](10) NULL,
[acad_user7] [VARCHAR](10) NULL,
[acad_user8] [VARCHAR](10) NULL,
[acad_user9] [VARCHAR](10) NULL,
[acad_user10] [VARCHAR](10) NULL,
[acad_acad_program] [VARCHAR](20) NULL,
[acad_cert_type] [VARCHAR](10) NULL,
[acad_cert_status] [VARCHAR](10) NULL,
[acad_cert_employable_by_code] [VARCHAR](10) NULL,
[acad_cert_staff_request_flag] [VARCHAR](1) NULL,
[acad_term] [VARCHAR](7) NULL,
[acad_cert_part_time_ind] [VARCHAR](10) NULL,
[acad_cert_part_time_ovr] [VARCHAR](1) NULL,
[acad_renewal_ind] [VARCHAR](1) NULL,
CONSTRAINT [PK_ACAD_CREDENTIALS] PRIMARY KEY CLUSTERED (
[acad_credentials_id] ASC )WITH (pad_index = OFF, statistics_norecompute =
OFF, ignore_dup_key = OFF, allow_row_locks = on, allow_page_locks = on,
FILLFACTOR = 90) ON [IDX]
)
ON [IDX]
textimage_on [PRIMARY]
go
SET ansi_padding ON
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT
[FK_ACAD_CREDENTIALS_ACAD_ACAD_LEVEL_OTHER_ACAD_LEVELS] FOREIGN KEY(
[acad_acad_level]) REFERENCES [dbo].[other_acad_levels] (
[other_acad_levels_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_ACAD_LEVEL_OTHER_ACAD_LEVELS]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_DEGREE_OTHER_DEGREES]
FOREIGN KEY([acad_degree]) REFERENCES [dbo].[other_degrees] (
[other_degrees_id]) NOT FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_DEGREE_OTHER_DEGREES]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT
[FK_ACAD_CREDENTIALS_ACAD_DIVISION_OTHER_DIVISIONS] FOREIGN KEY(
[acad_division]) REFERENCES [dbo].[other_divisions] ([other_divisions_id]) NOT
FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_DIVISION_OTHER_DIVISIONS]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT
[FK_ACAD_CREDENTIALS_ACAD_INSTITUTIONS_ID_INSTITUTIONS] FOREIGN KEY(
[acad_institutions_id]) REFERENCES [dbo].[institutions] ([institutions_id])
NOT FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_INSTITUTIONS_ID_INSTITUTIONS]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT
[FK_ACAD_CREDENTIALS_ACAD_LOCATION_OTHER_LOCATIONS] FOREIGN KEY(
[acad_location]) REFERENCES [dbo].[other_locations] ([other_locations_id]) NOT
FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_LOCATION_OTHER_LOCATIONS]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_PERSON_ID_PERSON]
FOREIGN KEY([acad_person_id]) REFERENCES [dbo].[person] ([id]) NOT FOR
REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_PERSON_ID_PERSON]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT
[FK_ACAD_CREDENTIALS_ACAD_TRANSCRIPT_COUNTRY_COUNTRIES] FOREIGN KEY(
[acad_transcript_country]) REFERENCES [dbo].[countries] ([countries_id]) NOT
FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_TRANSCRIPT_COUNTRY_COUNTRIES]
go
ALTER TABLE [dbo].[acad_credentials]
WITH NOCHECK ADD CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_TRANSCRIPT_STATE_STATES]
FOREIGN KEY([acad_transcript_state]) REFERENCES [dbo].[states] ([states_id])
NOT FOR REPLICATION
go
ALTER TABLE [dbo].[acad_credentials]
NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_TRANSCRIPT_STATE_STATES]
go
i am looking at this tutorial on creating a pivot, is that the way to go about this? I just dont see any other way.
Since I do not know much about the schema I can only guess. Seems like you need to use OUTER APPLY. How about this SELECT STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_LAST_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_FIRST_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_MIDDLE_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM, STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS, STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_START_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_END_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_ANT_CMPL_DATE, { fn CONCAT({ fn CONCAT(STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, '*') }, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL) } AS STUDENT_ACAD_LEVEL, STUDENT_ACAD_LEVELS_VIEW.STA_CLASS, PERSON.LAST_NAME AS UG_Inst, ACAD_CREDENTIALS.ACAD_DEGREE AS ACAD_DEGREE, ACAD_CREDENTIALS.ACAD_GPA AS ACAD_GPA, ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID AS ACAD_INSTITUTIONS_ID, ACAD_CREDENTIALS1.LAST_NAME AS UG_Inst1, ACAD_CREDENTIALS1.ACAD_DEGREE AS ACAD_DEGREE1, ACAD_CREDENTIALS1.ACAD_GPA AS ACAD_GPA1, ACAD_CREDENTIALS1.ACAD_INSTITUTIONS_ID AS ACAD_INSTITUTIONS_ID1 FROM STUDENT_ACAD_LEVELS_VIEW FULL OUTER JOIN STUDENT_ACAD_PROGRAMS_VIEW LEFT OUTER JOIN PERSON RIGHT OUTER JOIN ACAD_CREDENTIALS ON
PERSON.ID = ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID ON STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID = ACAD_CREDENTIALS.ACAD_PERSON_ID ON STUDENT_ACAD_LEVELS_VIEW.STA_STUDENT = STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID AND STUDENT_ACAD_LEVELS_VIEW.STA_ACAD_LEVEL = STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL OUTER APPLY ( SELECT TOP 1 PERSON.LAST_NAME UG_Inst, ACAD_DEGREE ACAD_DEGREE, ACAD_CREDENTIALS.ACAD_GPA ACAD_GPA, ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID ACAD_INSTITUTIONS_ID FROM ACAD_CREDENTIALS WHERE
PERSON.ID = ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID AND ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' ORDER BY ACAD_CREDENTIALS_ADDDATE ) ACAD_CREDENTIALS1 WHERE (STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS = 'Active') AND (ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID = '0000004') ORDER BY STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID
i fixed the error, you just had to put an alias at the end of the outer apply, I put "M"; however, this does not yield the right result. I need a extra collumns for if the person has a 2nd degree program or second school. Also, something went wrong with the 0000004 part because it is still displaying that code for the students who attend our school and i just needed those values to be null. thank you for your help, any other suggestions? how does outer apply work? I am a little fuzzy on the details
@Usman Butt Ok there was just a small syntax error I fixed but then It was populating all of the institutions with the name of our school and code 0000004 so i put my case statement back in so here is where I am at right now: SELECT STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_LAST_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_FIRST_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_MIDDLE_NAME, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM, STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS, STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_START_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_END_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_ANT_CMPL_DATE, { fn CONCAT({ fn CONCAT(STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, '*') }, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL) } AS STUDENT_ACAD_LEVEL, STUDENT_ACAD_LEVELS_VIEW.STA_CLASS, CASE WHEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' THEN PERSON.LAST_NAME ELSE NULL END AS UG_Inst, CASE WHEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' THEN ACAD_CREDENTIALS.ACAD_DEGREE ELSE NULL END AS ACAD_DEGREE, CASE WHEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' THEN ACAD_CREDENTIALS.ACAD_GPA ELSE NULL END AS ACAD_GPA, CASE WHEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' THEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID ELSE NULL END AS ACAD_INSTITUTIONS_ID, CASE WHEN ACAD_CREDENTIALS1.ACAD_INSTITUTIONS_ID <> '0000004' THEN PERSON.LAST_NAME ELSE NULL END AS UG_Inst1, CASE WHEN ACAD_CREDENTIALS1.ACAD_INSTITUTIONS_ID <> '0000004' THEN ACAD_CREDENTIALS.ACAD_DEGREE ELSE NULL END AS ACAD_DEGREE1, CASE WHEN ACAD_CREDENTIALS1.ACAD_INSTITUTIONS_ID <> '0000004' THEN ACAD_CREDENTIALS.ACAD_GPA ELSE NULL END AS ACAD_GPA1, CASE WHEN ACAD_CREDENTIALS1.ACAD_INSTITUTIONS_ID <> '0000004' THEN ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID ELSE NULL END AS ACAD_INSTITUTIONS_ID1 FROM STUDENT_ACAD_LEVELS_VIEW FULL OUTER JOIN STUDENT_ACAD_PROGRAMS_VIEW LEFT OUTER JOIN PERSON RIGHT OUTER JOIN ACAD_CREDENTIALS ON
PERSON.ID = ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID ON STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID = ACAD_CREDENTIALS.ACAD_PERSON_ID ON STUDENT_ACAD_LEVELS_VIEW.STA_STUDENT = STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID AND STUDENT_ACAD_LEVELS_VIEW.STA_ACAD_LEVEL = STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL OUTER APPLY ( SELECT TOP 1 PERSON.LAST_NAME UG_Inst, ACAD_DEGREE ACAD_DEGREE, ACAD_CREDENTIALS.ACAD_GPA ACAD_GPA, ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID ACAD_INSTITUTIONS_ID FROM ACAD_CREDENTIALS WHERE
PERSON.ID = ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID AND ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID <> '0000004' ORDER BY ACAD_CREDENTIALS_ADDDATE ) ACAD_CREDENTIALS1 WHERE (STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS = 'Active') AND (ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID = '0000004') ORDER BY STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID This also no longer returns the correct number of results... urghhhh :(
10 People are following this question.
