question

muk avatar image
muk 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
t-sqlsql-server-2008-r2sql-serverqueryquery-results
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
1 Like 1 ·
Yes, just the 2nd schools details and it doesnt matter which is 1st or 2nd but there are dates (this is by ACAD_CREDENTIALS_ADDDATE in ACAD_CREDENTIALS)
0 Likes 0 ·
ecomma avatar image
ecomma answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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!!
0 Likes 0 ·
muk avatar image
muk answered
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' 
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

muk avatar image
muk answered
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) 
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

muk avatar image
muk answered
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 
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

muk avatar image
muk answered
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 
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think that is all the tables and views necessary for this query. And what I have already come up with is at the top I just need to add if the person has a 2nd program or a 2nd school. I would appreciate any help at all. Thank you so much in advance.
0 Likes 0 ·
muk avatar image
muk answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
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
11 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have tried to correct it. See the edited answer.
1 Like 1 ·
@muk I have edited my answer again. Check it now.
1 Like 1 ·
Hi Usman, thank you for your response. I am getting the error incorrect syntax near the keyword 'WHERE' on line 30: WHERE (STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS = 'Active')
0 Likes 0 ·
It still says incorrect syntax near where on line 30. hmmmmm
0 Likes 0 ·
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
0 Likes 0 ·
Show more comments
muk avatar image
muk answered
@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 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.