February 6, 2008 at 4:29 pm
I need to find a students final grade from the transcriptdetail table, and i need it formatted using the max statement as i have below. BUT, some students have a final grade that associated with a transcript seq of 002, but not all. So i wanted to use a coelesce statment to pull that data, im not even sure though if thats the correct function i need. Ideally id like to use the max statement for formatting reasons, and pull their final grade based on a transcript seq of 002, IF, they have it, if not, then trans seq of 001. This is what i have so far....Anyhelp on what im missing here?
select p.first_name
,p.last_name
,p.people_code_id
,max(case when td.event_id ='SD 425' then ) as 'SD 425'
,max(case when td.event_id ='SD 426' then ) as 'SD 426'
,max(case when td.event_id ='SD 427' then ) as 'SD 427'
,max(case when td.event_id ='SD 428' then ) as 'SD 428'
,max(case when td.event_id ='SD 429' then ) as 'SD 429'
,max(case when td.event_id ='SD 430' then ) as 'SD 430'
inner join academic as a
on a.people_code_id=p.people_code_id
inner join peopletype as pt
on a.people_code_id=pt.people_code_id
left outer join transcriptdetail as td2
on p.people_code_id=td2.people_code_id
and academic_term='spring'
and academic_year='2007'
and transcript_seq='002'
and td2.final_grade <>'w'
left outer join transcriptdetail as td1
on p.people_code_id=td1.people_code_id
and academic_term='spring'
and academic_year='2007'
and transcript_seq='001'
and td1.final_grade <>'w'
where p.deceased_flag='n'
and pt.people_type='alum'
and a.graduated_year='2007'
and a.graduated_term='spring'
group by p.first_name
,p.last_name
,p.people_code_id
order by p.people_code_id
*edit*
Ideally i would like to return results in this format:
...................................................sd099 sd 100 ect.... final_grade
DanieltestP0000075552.0NULL2.72.42.02.02.14.0
February 6, 2008 at 5:09 pm
*** IGNORE THIS POST - GOT SENT WHILE MY CONNECTION WAS ACTING VERY STRANGELY ***
I need to find a students final grade from the transcriptdetail table, and i need it formatted using the max statement as i have below. BUT, some students have a final grade that associated with a transcript seq of 002, but not all. So i wanted to use a coelesce statment to pull that data, im not even sure though if thats the correct function i need. Ideally id like to use the max statement for formatting reasons, and pull their final grade based on a transcript seq of 002, IF, they have it, if not, then trans seq of 001. This is what i have so far....Anyhelp on what im missing here?
select p.first_name
,p.last_name
,p.people_code_id
,max(case when td.event_id ='SD 425' then ) as 'SD 425'
,max(case when td.event_id ='SD 426' then ) as 'SD 426'
,max(case when td.event_id ='SD 427' then ) as 'SD 427'
,max(case when td.event_id ='SD 428' then ) as 'SD 428'
,max(case when td.event_id ='SD 429' then ) as 'SD 429'
,max(case when td.event_id ='SD 430' then ) as 'SD 430'
, thisgrade (case transcript_seq
from sometable or other????
whatever joins and where clause you need
February 6, 2008 at 5:11 pm
?? I dont understand what your saying here, can you clairfy?
February 6, 2008 at 6:03 pm
OOOPS... lost connection
February 6, 2008 at 6:05 pm
Sorry - I keep losing connections here... in any case, if you post the schema
table names, columns/datatypes, keys (PK FK) , and some sample data, I can probably be of more help.
For your specific question of getting a value if the transcript_seq of '002' exists, you can try something like this... (without the schema, I have no way of knowing what joins to where, etc)
select p.first_name
,p.last_name
,p.people_code_id
,max(case when td.event_id ='SD 425' then ) as 'SD 425'
,max(case when td.event_id ='SD 426' then ) as 'SD 426'
,max(case when td.event_id ='SD 427' then ) as 'SD 427'
,max(case when td.event_id ='SD 428' then ) as 'SD 428'
,max(case when td.event_id ='SD 429' then ) as 'SD 429'
,max(case when td.event_id ='SD 430' then ) as 'SD 430'
, thisgrade = (case transcript_seq
when '002' then put_GRADE_FOR TRANSCRIPT OF '002' here
else put_GRADE_FOR TRANSCRIPT OF '001' here
end)
from sometable or other????
whatever joins and where clause you need
Toni
February 6, 2008 at 6:30 pm
Oh.. . and I am not sure what you are trying to accomplish with the
select ...,max(case when td.event_id ='SD 425' then ) as 'SD 425'
elements. They are incomplete and the max of a single item returned from the case statement would be that item - no?
Toni
February 7, 2008 at 9:03 am
Clarification:
MY Case statement is ,max(case when td.event_id ='SD 099' then td.final_grade else null end) as 'SD 099'
,max(case when td.event_id ='SD 100' then td.final_grade else null end) as 'SD 100'
,max(case when td.event_id ='SD 101' then td.final_grade else null end) as 'SD 101'
,max(case when td.event_id ='SD 102' then td.final_grade else null end) as 'SD 102'
,max(case when td.event_id ='SD 103' then td.final_grade else null end) as 'SD 103'
,max(case when td.event_id ='SD 104' then td.final_grade else null end) as 'SD 104'
The final_grade value is associated with their transcript seq, so ideally i would like it to display their final_grade associated with their transcript seq of 002, IF they have it, if not, then the final_grade should be displayed based on their transcript_seq of 001. Does that clairify?
The td.event_id is the class number, and i am putting together a list showing all possible classes for particular students wether they took it or not, so only the classes they completed would have their grade data, and the rest would just be null because they havent taken it.
February 7, 2008 at 10:15 am
Craig. The MAX portion adds nothing I can see since max(case when td.event_id ='SD 100' then td.final_grade else null end) as 'SD 100' still gives the single value which results from the case statement. As for the pulling of a grade from transcript 002 or transcript 001, can you supply the tables, their columns and key linkages? I still do not know what the p alias is linked to (p.people_code is from what table for instance?).
My guess at your select clause for extracting the final grade would be
finalgrade = (case transcript_seq
when '002' then (select final_grade
from sometableorother as p1
join transcriptdetail as td2
on p1.people_code_id=td2.people_code_id
and academic_term='spring'
and academic_year='2007'
and td2.transcript_seq='002'
and td2.final_grade <>'w')
else
(select final_grade from
sometableorother as p2
join transcriptdetail as td3
on p2.people_code_id=td3.people_code_id
and academic_term='spring'
and academic_year='2007'
and td3.transcript_seq='001'
and td3.final_grade <>'w')
end)
If you can supply the information on tables and columns, etc, we can go from there but this should get you on the right track I hope.
Toni
February 7, 2008 at 10:20 am
P is from the people table which primary key is people_code_id
The transcriptdetail table's primary keys are people_code_id, academic_year, academic_term, academic_session, event_id, event_sub_type, and section.
The reason i used the max before case is because i wanted to display ALL available classes(event_id) regardless if the student took it or not. If they took the class, and have a transcript_seq of 002, that final_grade should be displayed, if they dont have a transcript_seq of 002, then their transcript_seq of 001 should be used to pull the final_grade. Does this make sense?
Do you need more info?
February 7, 2008 at 10:50 am
I'm still not quite with you on that but... try this. If there are multiple values for event_id for a person (which I suspect), then you will need to adjust accordingly to cycle through the various values.
select p.first_name
,p.last_name
,p.people_code_id
,case
when td.event_id ='SD 100'
then
---------- pull the grade for this eventid -----
(case transcript_seq
when '002' then (select final_grade
from sometableorother as p1
join transcriptdetail as td2
on p1.people_code_id=td2.people_code_id
and academic_term='spring'
and academic_year='2007'
and td2.eventid= 'SD 425'
and td3.transcriptseq = '002'
and td2.final_grade <>'w')
When '001'
(select final_grade from
sometableorother as p2
join transcriptdetail as td3
on p2.people_code_id=td3.people_code_id
and academic_term='spring'
and academic_year='2007'
and td3.tdeventid='SD 425'
and td3.transcriptseq = '001'
and td3.final_grade <>'w')
else null
end)
else null
end) as 'SD 100'
,.... repeat for other td.eventid values
from people p
Otherwise I think I am losing it....
Toni
February 7, 2008 at 11:38 am
I think were close, but theres a few things that dont make sense.
1. when '001' ? Shouldnt this be when transcript_seq='001'? If so, what join do i use?
2. I only see the people table being joined, shouldnt i join the transcriptdetail table as td?
3. Whats the purpose of and td3.tdeventid='SD 425'? that would be a seprate class?
This is what i have so far, and it doesnt work. I get these errors.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'when'.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'else'.
select p.first_name
,p.last_name
,p.people_code_id
,case when td.event_id='sd 100' then (case transcript_seq when '002' then
(select final_grade from people as p
inner join transcriptdetail as td2
on p.people_code_id=td2.people_code_id
and td2.acadmemic_year='2007'
and td2.academic_term='spring'
and td2.transcript_seq='002'
and td2.final_grade<>'w'
when td.transcript_detail='001'
(select final_grade from people as p
inner join transcriptdetail as td3
on p.people_code_id=td3.people_code_id
and td2.acadmemic_year='2007'
and td2.academic_term='spring'
and td2.transcript_seq='001'
and td2.final_grade<>'w')
else null end)
from people as p
inner join transcript_detail as td
on p.people_code_id=td.people_code_id
February 7, 2008 at 11:47 am
This is closer, but still getting this error :
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'as'
select p.first_name
,p.last_name
,p.people_code_id
,case when td.event_id='sd 100' then (case transcript_seq when '002' then
(select final_grade from people as p
inner join transcriptdetail as td2
on p.people_code_id=td2.people_code_id
and td2.acadmemic_year='2007'
and td2.academic_term='spring'
and td2.transcript_seq='002'
and td2.final_grade<>'w')
when '001'then
(select final_grade from people as p
inner join transcriptdetail as td3
on p.people_code_id=td3.people_code_id
and td3.acadmemic_year='2007'
and td3.academic_term='spring'
and td3.transcript_seq='001'
and td3.final_grade<>'w')
else null end) as 'sd 100'
from people as p
inner join transcript_detail as td
on p.people_code_id=td.people_code_id
February 7, 2008 at 12:16 pm
You only ended the inner CASE statement. You need another "END" before "AS" to end the first CASE.
February 7, 2008 at 12:49 pm
I did this
and td3.transcript_seq='001'
and td3.final_grade<>'w')
else null end) else null end) as 'sd 100'
from people as p
inner join transcript_detail as td
on p.people_code_id=td.people_code_id
and receive the same error, can you specifiy? Thanks!
February 7, 2008 at 1:03 pm
As was said .... you need to end the outer case statement
select p.first_name
,p.last_name
,p.people_code_id
,case when td.event_id='sd 100' then (case transcript_seq when '002' then
(select final_grade from people as p
inner join transcriptdetail as td2
on p.people_code_id=td2.people_code_id
and td2.acadmemic_year='2007'
and td2.academic_term='spring'
and td2.transcript_seq='002'
and td2.final_grade<>'w')
when '001'then
(select final_grade from people as p
inner join transcriptdetail as td3
on p.people_code_id=td3.people_code_id
and td3.acadmemic_year='2007'
and td3.academic_term='spring'
and td3.transcript_seq='001'
and td3.final_grade<>'w')
else null end) end as 'sd 100'
from people as p
inner join transcript_detail as td
on p.people_code_id=td.people_code_id
Toni
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply