Actually, I'm having some trouble converting over the example to my sample table above. I just took the grades for 03-01-2012. The median should be the average between 90 and 91 (so 90.5), correct? This gets that result set:
SELECT grade
FROM estats
WHERE CourseDate = '2012-03-01'
ORDER BY grade DESC
But when I run the below (which is an attempt to convert the example to my sample data above, I get 89.25. I'm sure I'm making a dumb mistake somewhere, but any help is appreciated.
WITH cte AS
(
SELECT EStats.PersonID, EStats.Grade,
ROW_NUMBER() OVER(PARTITION BY EStats.PersonID ORDER BY EStats.GRADE) AS RN,
COUNT(*) OVER(PARTITION BY EStats.PersonID) AS Cnt
FROM EStats
WHERE EStats.CourseDate = '2012-03-01'
)
SELECT AVG(CAST(cte.Grade AS Numeric)) AS Median
FROM cte
WHERE RN IN((cte.Cnt + 1) / 2, (Cnt + 2) / 2)