Percentile CalculationL

MSSQL 2008/R2


SELECT [AssetClassId],[Period],
MIN(CASE WHEN seqnum >= 0.05 * cnt THEN [ValueInDecimal] END) as percentile_05
FROM (select t.*,
row_number() over (partition by [AssetClassId],[Period] order by [ValueInDecimal]) as seqnum,
count(*) over (partition by [AssetClassId],[Period]) as cnt
from [dbo].[ModelData1] t
) t
GROUP BY [AssetClassId], [Period]
ORDER BY [AssetClassId], [Period]

 
MSSQL 2012

SELECT MAX([5thPercentile]),MAX([25thPercentile])
FROM
(
SELECT [5thPercentile] = PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY [ValueInDecimal] ) OVER (),
[25thPercentile] = PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY [ValueInDecimal] ) OVER ()
FROM [dbo].[ModelData1] T2
WHERE T2.[AssetClassId] = 10024 and T2.Period = 1
)T1