Calculating Median in SQL Server 2008 R2

Question

I need to calculate Median for several fields in a query and group it by one of the column. Is there a way to calculate Median easily in SQL Server 2008 R2? I am having trouble calculating it in 2008 R2.

Table structure:
PatientName (need to calculate count group by PatientType)
PatientType (should be used to group the query by),
minutes1,
minutes2,
minutes3,
minutes4,
minutes5

End Result:
PatientCount (Group by PatientType),
Median For minutes1 (Group by PatientType),
Median For minutes2 (Group by PatientType),
Median For minutes3 (Group by PatientType),
Median For minutes4 (Group by PatientType),
Median For minutes5 (Group by PatientType)

Answer 1

You could try this:

SELECT PatientType, minutes1=(
  SELECT AVG(1.0 * minutes1)
  FROM
  (
    SELECT t3.minutes1, rn = ROW_NUMBER() OVER (ORDER BY t3.minutes1), c.c
    FROM (SELECT minutes1 FROM Table t2 WHERE t1.PatientType=t2.PatientType) t3
    CROSS JOIN (SELECT c = COUNT(*) FROM (SELECT minutes1 FROM Table t2 WHERE t1.PatientType=t2.PatientType) t4) AS c
  ) AS x
  WHERE rn IN ((c + 1)/2, (c + 2)/2)
), minutes2=(
  SELECT AVG(1.0 * minutes2)
  FROM
  (
    SELECT t3.minutes2, rn = ROW_NUMBER() OVER (ORDER BY t3.minutes1), c.c
    FROM (SELECT minutes2 FROM Table t2 WHERE t1.PatientType=t2.PatientType) t3
    CROSS JOIN (SELECT c = COUNT(*) FROM (SELECT minutes2 FROM Table t2 WHERE t1.PatientType=t2.PatientType) t4) AS c
  ) AS x
  WHERE rn IN ((c + 1)/2, (c + 2)/2)
), minutes3=(
  SELECT AVG(1.0 * minutes1)
  FROM
  (
    SELECT t3.minutes3, rn = ROW_NUMBER() OVER (ORDER BY t3.minutes1), c.c
    FROM (SELECT minutes3 FROM Table t2 WHERE t1.PatientType=t2.PatientType) t3
    CROSS JOIN (SELECT c = COUNT(*) FROM (SELECT minutes3 FROM Table t2 WHERE t1.PatientType=t2.PatientType) t4) AS c
  ) AS x
  WHERE rn IN ((c + 1)/2, (c + 2)/2)
), minutes4=(
  SELECT AVG(1.0 * minutes4)
  FROM
  (
    SELECT t3.minutes1, rn = ROW_NUMBER() OVER (ORDER BY t3.minutes1), c.c
    FROM (SELECT minutes4 FROM Table t2 WHERE t1.PatientType=t2.PatientType) t3
    CROSS JOIN (SELECT c = COUNT(*) FROM (SELECT minutes4 FROM Table t2 WHERE t1.PatientType=t2.PatientType) t4) AS c
  ) AS x
  WHERE rn IN ((c + 1)/2, (c + 2)/2)
), minutes5=(
  SELECT AVG(1.0 * minutes5)
  FROM
  (
    SELECT t3.minutes1, rn = ROW_NUMBER() OVER (ORDER BY t3.minutes1), c.c
    FROM (SELECT minutes5 FROM Table t2 WHERE t1.PatientType=t2.PatientType) t3
    CROSS JOIN (SELECT c = COUNT(*) FROM (SELECT minutes5 FROM Table t2 WHERE t1.PatientType=t2.PatientType) t4) AS c
  ) AS x
  WHERE rn IN ((c + 1)/2, (c + 2)/2)
)
FROM Table t1
GROUP BY PatientType

and there is probably a much better way, and it could likely be optimized quite a bit.