please help with this query still no response

Question

question

muk asked Jun 28, '12

please help with this query still no response

[link text][1]Hi all, I am still trying to get this report resolved. 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 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!! [1]: /storage/temp/277-ddl.txt

t-sql sql-server query reporting

ddl.txt (43.7 KiB)

14

10 |1200

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

Fatherjack ♦♦ commented · Jun 28, 2012 at 08:30 PM

@muk, is this the same problem that you have reposted? This wont lead to anything but confusion. The same people will read this question and offer the same advice as they have or, more likely, get tired of reading the same question and give up trying to help you. Please consider that all people on this forum give their time and advice for free so you need to make it as easy as possible for us to get the idea of your problem and give advice and guidance.

1 ·

Oleg commented · Jun 28, 2012 at 09:50 PM

@muk The list of columns is not dynamic, it is static, so if you have 2 people (2 records in your results), one of which has one record and the other one has 2 records in the acad\_credentials table then your columns still include a pair for each from 4 on your list. It is just the first record will have null values in the second of each pair's values if the person attended but one school. One thing which is very difficult to understand is the usage of full, left and right outer joins in your query. Ar you sure you need them the way they are written? The reports should never include any blank records so all your joins should be INNER. Also, please clarify the most important thing: what do you want to do with people who attended 3 or more schools? You stated that you only need to include the info about 2, so there has to be some exclusion criteria which you did not list (the rule you need to use to pick only 2 records from acad\_credentials for the person which has more than 2 records).

1 ·

Fatherjack ♦♦ commented · Jun 29, 2012 at 11:43 AM

@muk - I understand that you didnt get an answer on the other question but putting two questions up here means that one will always be unanswered but will probably have comments that are useful on it. This dilutes the use of the forum to others with similar problems who search through for ideas on how to resolve their own situation. We'd appreciate it if you limit yourself to asking one question once. you can always edit the question and add an update on the progress you are making in investigating the issue for yourself, to bring it to the top of the pile. It is wholly possible that a question will takes several days to get answered. If you have an urgent situation then you may need to consider hiring a consultant or opening a support case with Microsoft rather than rely on a forum like this.

1 ·

Oleg commented · Jun 28, 2012 at 07:48 PM

@muk So far it looks like the query was generated by some sort of tool, people usually do not bunch the joins together first and then list a whole bunch of the ON conditions together. Also, it would be nice if you could remove the DDL part out of the question text and instead attach it as a file because the formatting is so off now that it is difficult to fix it by hand.

0 ·

muk commented · Jun 28, 2012 at 07:51 PM

done and done! and yes, I used the query wizard in SSRS.

0 ·

Show more comments

Answer 1 · 2012-06-29T14:57:47Z

muk answered Jun 29, '12

[link text][1] [1]: /storage/temp/281-data.txt Here are the collumn headers: STUDENT_ID STUDENT_LAST_NAME STUDENT_FIRST_NAME STUDENT_MIDDLE_NAME STP_START_DATE STUDENT_ACAD_LEVELS_ID STA_CLASS Years Column2 Column1 Column12 Column13 as400ugador inst as400ugador degree as400ugador inst2 as400ugador degree22 UG_Inst ACAD_DEGREE STU_REMARKS Dont worry about: Years Column2 Column1 Column12 Column13 as400ugador inst as400ugador degree as400ugador inst2 as400ugador degree22 There were from our previous unidata system

data.txt (3.2 KiB)

3

10 |1200

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

Oleg commented · Jun 29, 2012 at 03:05 PM

@muk You said you have up to 3 records per student in the **student\_acad\_programs\_view** and also that there are multiple records in the **acad\_credentials**, up to 2. So, how many records should you see in the report for such student? Also, in the sample data I did not see any students who have attended more than one school. There are some students with 2 records but both records indicate the same school

0 ·

muk commented · Jun 29, 2012 at 04:54 PM

It should be one record per student summarizing all of their data.

0 ·

muk commented · Jun 29, 2012 at 04:56 PM

The student with the same school means he may have obtained 2 degrees from that school

0 ·

Answer 2 · 2012-06-29T15:25:35Z

ecomma answered Jun 29, '12

1. Please list down the name of your tables. 2. state the primary key and foreign key for each. 3. After that,i will ask you another question and you will have your problem resolved today.

1

10 |1200

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

muk commented · Jun 29, 2012 at 04:58 PM

I will attach the DDL's in the next answer I submit.

0 ·

Answer 3 · 2012-06-29T17:01:28Z

muk answered Jun 29, '12

[link text][1] [1]: /storage/temp/282-check.txt @ecomma, I attached the DDLS I scripted from SSMS to create to clipboard. Let me know if there is anything else you may need. Thanks again.

check.txt (31.0 KiB)

10 |1200

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

question