The query below currently returns the max(SATTOTAL) but I need to return the SATTOTAL associated with the latest studentstest.test_date instead. I can't figure out how to return the value based on that criteria. Can anyone help? Thanks!!
STUDENT_NUMBER as "StuNum",
max(CMTSCIENCE) AS "Grade 8 CMT Science",
max(SATTOTAL) AS "Grade 8 CMT Science"
students.student_number AS STUDENT_NUMBER,
CASE WHEN UPPER(Test.Name) = 'CMT' AND StudentTest.GRADE_LEVEL = '8' AND TestScore.NAME = 'Science Scale Score'
THEN StudentTestScore.numscore ELSE NULL END AS CMTSCIENCE,--"Grade 8 CMT Science"
CASE WHEN UPPER(Test.Name) = 'SAT' AND TestScore.NAME = 'Total Score'
THEN StudentTestScore.numscore ELSE NULL END AS SATTOTAL,--"Latest SAT Total"
LEFT JOIN StudentTestScore ON StudentTestScore.StudentID = Students.ID
LEFT JOIN StudentTest ON StudentTestScore.StudentTestID = StudentTest.ID
LEFT JOIN Test ON StudentTest.TestID = Test.ID
LEFT JOIN TestScore ON StudentTestScore.TestScoreID = TestScore.ID
StudentTestScore.NumScore > 0
AND Students.STUDENT_NUMBER IN ('3023484','3010864')
CMTSCIENCE > 0
OR SATTOTAL > 0