quote:

---

Originally posted by jpost

I am trying to pull results for test scores where if the student failed a state mandated test, that I can create a column that list "still needs test Name" The first credential that I pull is if they scored less than or = to 399. If I get multiple results for the same test, then I need to have them marked as needing to take this test to graduate. Hope this makes sense? Any suggestions would be great.
Thanks

---

SELECT
	s.studentNumber,
	s.firstName,
	s.lastname,
	t.name,
	MAX(ts.scaleScore),
	CASE 
		WHEN MAX(ts.scaleScore) <= 399 THEN 'RETAKE'
		ELSE 'PASS'
	END AS [Pass/Fail]
FROM
	TestScore ts
	INNER JOIN Test t
		ON  ts.testID = t.testID
	INNER JOIN Student s
		ON  ts.personID = s.personID
WHERE
	ts.scaleScore IS NOT NULL
	AND s.studentNumber = '314207'
GROUP BY
	s.studentNumber,
	s.firstName,
	s.lastname,
	t.name,

--HAVING
--	MAX(ts.scaleScore) <= 399;

quote:

---

Originally posted by jpost

399 is just a mark per test
Here is some of the code I am using, I thought if I could have the sum of the last column to be less the 1, then they would have to retake the test.

select distinct s.studentNumber,s.firstName,s.lastName,ts.date, t.name, ts.scaleScore,
case
when ts.scaleScore <= 399 then 0
else 1
end as 'Pass/Fail'
from TestScore ts
Inner join Test t on ts.testID = t.testID
Inner join Student s on ts.personID = s.personID
where ts.scaleScore is not null and s.studentNumber ='314207'
order by t.name

Results
StudentNum Last First Date Test Score pass/fail

314207 Genevieve Boateng 2012-03-01 00:00:00 OGT MATHEMATICS 391.000 0
314207 Genevieve Boateng 2012-07-01 00:00:00 OGT MATHEMATICS 389.000 0
314207 Genevieve Boateng 2012-03-01 00:00:00 OGT READING 383.000 0
314207 Genevieve Boateng 2012-07-01 00:00:00 OGT READING 415.000 1
314207 Genevieve Boateng 2012-03-01 00:00:00 OGT SCIENCE 389.000 0
314207 Genevieve Boateng 2012-07-01 00:00:00 OGT SCIENCE 386.000 0
314207 Genevieve Boateng 2012-03-01 00:00:00 OGT SOCIAL STUDIES 403.000 1
314207 Genevieve Boateng 2012-03-01 00:00:00 OGT WRITING 413.000 1

---

quote:

---

Originally posted by jpost

sunitabeck, I thought that what you provided would be adequate for the people requesting the report, however, they want to be able to pull info for test per student that would still need to be taken. So they would get a student number name, and a list of tests still needed.

visakh16- I thought about the sum as a possibility for needing a retake. But with a wide range of possible results, not sure how this would work. Example, lets say student received a 300 on Math the first time and a 410 the second time. The sum would be 710, but I could have a student that had 350 and 360 and still get the same sum, so I am not sure what I would need to do

---

SELECT *
TestScore ts
	INNER JOIN Test t
		ON  ts.testID = t.testID
	INNER JOIN Student s
		ON  ts.personID = s.personID
WHERE ts.scalescore < =399
AND NOT EXISTS (SELECT 1
                FROM Testscore
                WHERE testID = ts.testID
                AND personID = ts.personID
                AND Date > ts.Date
                AND scalescore > 399
                )