Below another attempt, but I seem to be running into the same problem. For instance, 2012-03-01 should produce 90.5, but the below gives me 89.25.

SELECT

CourseDate,

AVG(CAST(Grade AS Numeric))

FROM

(

SELECT

CourseDate,

Grade,

ROW_NUMBER() OVER (

PARTITION BY PersonID

ORDER BY Grade ASC, CourseDate ASC) AS RowAsc,

ROW_NUMBER() OVER (

PARTITION BY PersonID

ORDER BY Grade DESC, CourseDate DESC) AS RowDesc

FROM EStats

)x

WHERE

RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)

GROUP BY CourseDate

ORDER BY CourseDate

UPDATE: I think the problem is my PARTITION BY isn't working. In other words, the inner query produces a row_number with all 1's so there is nothing to match. Any ideas on a fix?