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 ·

muk commented · Jun 28, 2012 at 08:39 PM

This is the same problem but no one helped lead me to an answer and I still do not have a solution so I reposted it so it wont just disappear in the forums lol. And I reposted it with more clarity because before I was posting everything as an answer which may have also lead people away from looking at my post. I understand that people are giving time and advice for free but I really havent gotten any on this question yet so I was hoping someone else out there might notice. I'm new here, sorry if this bothered you.

0 ·

muk commented · Jun 28, 2012 at 09:55 PM

I did the joins this way because There can be students that have no institutions attended. so the institutions could be blank, i guess maybe im wrong, I am very new at this. Also, there are no people that have attended 3 or more schools. The database pulls information from a software we use here and students are only given the option of putting up to 2 schools. Thanks again for your attention, my boss is grinding at me to get this report done and I told him it is pretty complicated. :-x

0 ·

muk commented · Jun 28, 2012 at 09:55 PM

I used the auto-detect feature in SSRS to do the joins, maybe it joined incorrectly ?

0 ·

Oleg commented · Jun 28, 2012 at 10:06 PM

@muk OK, I am not sure about the joins, but then again, I am yet to ever use a tool to write my queries. From what I know, you have students registered in the system and they could register no more than 2 schools which they attended (it is OK to register none, the students have to be included in the results anyway). I don't have any time now, but will try to have something for you early tomorrow morning. In a mean time, please check this line for me: ON PERSON.ID = ACAD_CREDENTIALS.ACAD_INSTITUTIONS_ID Are you sure that this is correct condition?

0 ·

muk commented · Jun 29, 2012 at 02:01 PM

@Oleg, This condition is to get the name of the school. the name of the school is stored in the person table and it's person id is the same as the institutions id

0 ·

muk commented · Jun 29, 2012 at 02:02 PM

@Fatherjack, I see your point. Sorry

0 ·

Oleg commented · Jun 29, 2012 at 02:39 PM

@muk OK, you stated that **acad\_credentitals** may have 0, 1, or a maximum of 2 records per student. What about the **student\_acad\_programs\_view**? How many records does this view have per student? Also, on the top of selecting student's last name from the acad programs, you also for some reason include the last name from the person table, but it appears that the last name of person is the same as student last name from acad programs. If this is the case then why do you have to involve the person table? Please let me know because I am trying to come up with the query for you but it is difficult without the sample data.

0 ·

muk commented · Jun 29, 2012 at 02:46 PM

STUDENT_ACAD_PROGRAMS_VIEW has one record per student per program so a student can have multiple programs, therefore, they may have multiple records in this view (up to 3). the name of the academic institution is only stored in the person table so the only way to get the name of the UG_Inst is to get the academic institution id and then get the name of the instution by this id from the person table. let me see if I can come up with a little sample data for you. Would it be helpful to pull 2 records from each view/table?

0 ·

muk commented · Jun 29, 2012 at 02:55 PM

I have attached a sample of the data

0 ·

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