Listing 4: Filtering Rows for Median Calculation

WITH OrdersRN AS
(
  SELECT EmployeeID, Value,
    ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY Value) AS RowNum,
    COUNT(*) OVER(PARTITION BY EmployeeID) AS Cnt
  FROM dbo.VOrders
)
SELECT EmployeeID, Value, RowNum, Cnt
FROM OrdersRN
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2);