I have had to use OLAP cube MEDIAN function to calculate a median value in the SSRS report. I can’t believe how you can now use this new function in SQL Server 2012 to achieve the same! 🙂 This new function is not available in versions before SQL Server 2012.
An example using AdventureWorks2012:
SELECT d.Name as DepartmentName, Rate
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ph.Rate) Â OVER (PARTITION BY Name) AS MedianCont
FROM HumanResources.Department AS d
INNER JOIN HumanResources.EmployeeDepartmentHistory AS dh
ON dh.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.EmployeePayHistory AS ph
ON ph.BusinessEntityID = dh.BusinessEntityID
WHERE dh.EndDate IS NULL
AND d.Name in (‘Engineering’, ‘Document Control’, ‘Executive’, ‘Human Resources’)
ORDER BY Â DepartmentName, Rate
The following shows Rate for each Department in an ascending order:
The result of using the PERCENTILE_CONT to get a median Rate value for each department is shown below:
Leave a comment