June 9, 2008 at 8:48 am
My goal is to have one row per Student_Number and Test_Date. Not quite sure how to approach this. Sample data and sql below.
*****************************************
StudentID*Student_Number*Test_Date*Grade_level*Math_Num*Math_alpha*Read_Num*Read_alpha*Write_Num*Write_Alpha
431188002/01/200610176PNULLNULLNULLNULL
431188010/01/200610NULLNULLNULLNULL173P
431188010/01/200610NULLNULL182PNULLNULL
531188102/01/200610168PNULLNULLNULLNULL
531188110/01/200610NULLNULLNULLNULL165P
531188110/01/200610NULLNULL172PNULLNULL
631188202/01/200610175PNULLNULLNULLNULL
631188210/01/200610NULLNULLNULLNULL173P
631188210/01/200610NULLNULL181PNULLNULL
**************************************
Select
zc.stuid as StudentID,
st.suniq as Student_Number,
convert(varchar,st.takendt,101) as Test_Date,
st.graden as Grade_level,
case
when sc.subtestc = 3 then sc.testscore
end as Math_Num,
case
when sc.subtestc = 3 and sc.testscore > 160 then 'P'
end as Math_Alpha,
case
when sc.subtestc = 1 then sc.testscore
end as Read_Num,
case
when sc.subtestc = 1 and sc.testscore > 160 then 'P'
end as Read_Alpha,
case
when sc.subtestc = 2 then sc.testscore
end as Write_Num,
case
when sc.subtestc = 2 and sc.testscore > 160 then 'P'
end as Write_Alpha
from
zz_suniq_conv zc
join stutests1 st on st.suniq = zc.suniq
join stutscors1 sc on st.ststuniq = sc.ststuniq
where
sc.testc = 'UBSCT'
group by
zc.stuid,
st.suniq,
st.takendt,
st.graden,
sc.testscore,
sc.subtestc
order by zc.stuid
June 9, 2008 at 8:51 am
This does look like homework.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2008 at 9:12 am
Does my lack of sql knowledge make me appear to be a student?
June 9, 2008 at 9:27 am
It looks to me like you could Group By student number and test date, and just use max() for getting the grades. Aggregates eliminate nulls.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2008 at 9:40 am
Opus (6/9/2008)
Does my lack of sql knowledge make me appear to be a student?
No. Nor do I care if you are a student, in fact I still consider myself a student. This issue is whether this is homework and to what extent it is OK for us to help you with it.
If it isn't, then just say so.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2008 at 10:10 am
It's not homework.
June 9, 2008 at 10:36 am
Thanks GSquared. That worked.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply