Listing 3: Calculating Median Value per Employee by Using Subqueries

SELECT EmployeeID,
(
 (SELECT MAX(Value) FROM
   (SELECT TOP 50 PERCENT Value FROM dbo.VOrders AS O1
    WHERE O1.EmployeeID = E.EmployeeID
    ORDER BY Value) AS H1)
 +
 (SELECT MIN(Value) FROM
   (SELECT TOP 50 PERCENT Value FROM dbo.VOrders AS O2
    WHERE O2.EmployeeID = E.EmployeeID
    ORDER BY Value DESC) AS H2)
) / 2 AS Median
FROM dbo.Employees AS E;