May 27, 2003 at 3:52 pm
Hello,
I am using CASE statments to set up a crosstab report in SQL Server 2000, and I need help with an additional condition I need to express in the CASE statements (or IF statements). The following is the code I have so far:
*********************************************************
SELECT
SD.permnum,
SD.firstname,
SD.lastname,
SD.grade,
TT.firstname AS TeacherFName,
TT.lastname AS TeacherLName,
SUM(CASE SC.TestShortName WHEN 'HMRP' THEN SC.TestScore ELSE NULL END) AS PreTest,
--Need to find same as above when SC.TestScore < 70
SUM(CASE SC.TestShortName WHEN 'HMR1' THEN SC.TestScore ELSE NULL END) AS FirstQT
--Need to find same as above when SC.TestScore < 60
FROM student_data_main SD
LEFT OUTER JOIN teacher_data_main TT ON SD.teacherid=TT.teacherid
INNER JOIN tblMMStudentTestScores SC ON SC.Permnum=SD.Permnum
WHERE
TT.systemusername = 'valverde\KSidney'
GROUP BY SD.Permnum, SD.FirstName, SD.LastName, SD.Grade, TT.FirstName, TT.LastName
ORDER BY PreTest DESC, FirstQT DESC
***********************************************
This gives me the results I expect, but I also need to set up fields CASE SC.TestShortName WHEN 'HMRP' THEN SC.TestScore ELSE NULL END WHERE SC.TestScore < 70.
These fields would be set equal to expressions much like both of the Aggregate statements above, but would include the conditions that one field would express records where SC.TestScore < 70 and the other where SC.TestScore < 60.
I thought I might do this with 'IF' statements, but I could not find a good example of this.
If you have any ideas on this, please let me know.
Thanks for your help!
CSDunn
May 28, 2003 at 12:18 am
is this what you are looking for
SELECT
SD.permnum,
SD.firstname,
SD.lastname,
SD.grade,
TT.firstname AS TeacherFName,
TT.lastname AS TeacherLName,
SUM(CASE SC.TestShortName
WHEN 'HMRP' THEN
CASE
WHEN SC.TestScore < 70 THEN SC.TestScore
ELSE NULL
END
ELSE NULL
END) AS PreTest,
SUM(CASE SC.TestShortName
WHEN 'HMR1' THEN
CASE
WHEN SC.TestScore < 60 THEN SC.TestScore
ELSE NULL
END
ELSE NULL
END) AS FirstQT
--Need to find same as above when SC.TestScore < 60
FROM student_data_main SD
LEFT OUTER JOIN teacher_data_main TT ON SD.teacherid=TT.teacherid
INNER JOIN tblMMStudentTestScores SC ON SC.Permnum=SD.Permnum
WHERE
TT.systemusername = 'valverde\KSidney'
May 28, 2003 at 4:22 am
I think you are looking for this
SUM(CASE SC.TestShortName WHEN 'HMRP' THEN SC.TestScore ELSE NULL END) AS PreTest,
SUM(CASE WHEN SC.TestShortName = 'HMRP' AND SC.TestScore < 70 THEN SC.TestScore ELSE NULL END) AS PreTest70,
SUM(CASE SC.TestShortName WHEN 'HMR1' THEN SC.TestScore ELSE NULL END) AS FirstQT,
SUM(CASE WHEN SC.TestShortName = 'HMR1' AND SC.TestScore < 60 THEN SC.TestScore ELSE NULL END) AS FirstQT60
May 28, 2003 at 10:52 am
Thanks for your help, I had not worked with CASE in either of these ways before.
CSDunn
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply