September 24, 2007 at 10:55 am
Scenario: A student can take a particular test (reading, in this case) 5 times before graduating. A passing score is 160 or above. What I'm looking for is the query that will give me the students highest score under 160 if they have not passed the test with a 160 or above.
The sql below gives me the highest score under 160 but it's not excluding the student if they have a passing score.
For example, little Suzy took the Reading test 4 times with scores of 148, 155, 158 & 167. The first three are non-passing and 167 is passing. When I run the sql below I would hope Suzy is not in the result set due to the 167 score, but I have her listed with the 158 score.
Suggestions?
-------------------------------------------------------------
SELECT
t.schoolc as School_Code,
RTRIM(k.schname) as School,
s.ident as Ident,
RTRIM(s.lastname) + ', ' + RTRIM(s.firstname) AS Student,
ss.graden as [Current Grade],
left(sb.descript,20) as Subject,
max(sc.testscore) as Score
FROM
stutscors1 as sc INNER JOIN
stutests1 as st ON sc.ststuniq = st.ststuniq INNER JOIN
studemo as s ON st.suniq = s.suniq INNER JOIN
stustat as ss ON s.stuuniq = ss.stuuniq INNER JOIN
track as t ON ss.trkuniq = t.trkuniq INNER JOIN
school as k ON t.schoolc = k.schoolc INNER JOIN
testdef1 as td on st.testuniq = td.testuniq INNER JOIN
zsubtest1 as sb on sc.subtestc = sb.subtestc and sc.testc = sb.testc
WHERE
sc.testscore <> ' ' and
sc.testscore <> '0' and
k.schname = 'Dixie High' and
sc.testc = 'UBSCT' and
ss.graden in ('11', '12') and
sb.descript = 'Reading' and
sc.testscore <= '159'
GROUP BY
t.schoolc,
RTRIM(k.schname),
s.ident,
RTRIM(s.lastname) + ', ' + RTRIM(s.firstname),
ss.graden,
left(sb.descript,20)
ORDER BY
ss.graden desc,
RTRIM(s.lastname) + ', ' + RTRIM(s.firstname),
s.ident
September 24, 2007 at 11:15 am
Add a subquery that gets the people that have passed.
So a
...
and studentid not in (select studentid from xx where score > 160)
September 24, 2007 at 11:15 am
i think this is nothing more than adding a HAVING statement to the query you posted:you might need to select max(score), testcount as a separate subquery instead because of all the where statments.
SELECT
t.schoolc as School_Code,
RTRIM(k.schname) as School,
s.ident as Ident,
RTRIM(s.lastname) + ', ' + RTRIM(s.firstname) AS Student,
ss.graden as [Current Grade],
left(sb.descript,20) as Subject,
max(sc.testscore) as Score
FROM
stutscors1 as sc INNER JOIN
stutests1 as st ON sc.ststuniq = st.ststuniq INNER JOIN
studemo as s ON st.suniq = s.suniq INNER JOIN
stustat as ss ON s.stuuniq = ss.stuuniq INNER JOIN
track as t ON ss.trkuniq = t.trkuniq INNER JOIN
school as k ON t.schoolc = k.schoolc INNER JOIN
testdef1 as td on st.testuniq = td.testuniq INNER JOIN
zsubtest1 as sb on sc.subtestc = sb.subtestc and sc.testc = sb.testc
WHERE
sc.testscore <> ' ' and
sc.testscore <> '0' and
k.schname = 'Dixie High' and
sc.testc = 'UBSCT' and
ss.graden in ('11', '12') and
sb.descript = 'Reading' and
sc.testscore <= '159'
GROUP BY
t.schoolc,
RTRIM(k.schname),
s.ident,
RTRIM(s.lastname) + ', ' + RTRIM(s.firstname),
ss.graden,
left(sb.descript,20)
HAVING COUNT(sc.testscore) < 5
ORDER BY
ss.graden desc,
RTRIM(s.lastname) + ', ' + RTRIM(s.firstname),
s.ident
Lowell
September 24, 2007 at 11:17 am
Try this:
SELECT
t.schoolc as School_Code,
RTRIM(k.schname) as School,
s.ident as Ident,
RTRIM(s.lastname) + ', ' + RTRIM(s.firstname) AS Student,
ss.graden as [Current Grade],
left(sb.descript,20) as Subject,
max(sc.testscore) as Score
FROM
stutscors1 as sc
INNER JOIN stutests1 as st
ON (sc.ststuniq = st.ststuniq)
INNER JOIN studemo as s
ON (st.suniq = s.suniq)
INNER JOIN stustat as ss
ON (s.stuuniq = ss.stuuniq)
INNER JOIN track as t
ON (ss.trkuniq = t.trkuniq)
INNER JOIN school as k
ON (t.schoolc = k.schoolc)
INNER JOIN testdef1 as td
ON (st.testuniq = td.testuniq)
INNER JOIN zsubtest1 as sb
ON (sc.subtestc = sb.subtestc
and sc.testc = sb.testc)
WHERE
sc.testscore <> ' '
and sc.testscore <> '0'
and k.schname = 'Dixie High'
and sc.testc = 'UBSCT'
and ss.graden in ('11', '12')
and sb.descript = 'Reading'
-- and sc.testscore <= '159'
GROUP BY
t.schoolc,
RTRIM(k.schname),
s.ident,
RTRIM(s.lastname) + ', ' + RTRIM(s.firstname),
ss.graden,
left(sb.descript,20)
HAVING
max(sc.testscore) <= '159'
ORDER BY
ss.graden desc,
RTRIM(s.lastname) + ', ' + RTRIM(s.firstname),
s.ident
September 24, 2007 at 11:57 am
Danke!!! So close yet so far.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply