# Calculate Percentiles with MS-SQL 2005 using interpolation…

You’ll need MS SQL 2005 or above, it looks like version 2012 has it built in see here!
This is my first use of the OVER bit so it may be a little ugly, also I have been using the old way to pivot stuff so it’s a bit messy at the end.

```declare @t table (percentile numeric(10,5))
insert into @t values (0)
insert into @t values (0.25)
insert into @t values (0.5)
insert into @t values (0.75)
insert into @t values (0.8)
insert into @t values (1.0)

--give rownumbers for your data over a partition
drop table ##mgw
SELECT
s.gppraccode
,age
,ROW_NUMBER() OVER (partition by gppraccode ORDER BY age) AS "RowNumber"
INTO ##mgw
FROM mgw_ane_base AS s

--calculate the individual values per percentile
drop table ##mgw_percentileHolding
select	t.percentile*(max(rownumber)-1)+1 as act
,floor(t.percentile*(max(rownumber)-1)+1) as K
,((t.percentile*(max(rownumber)-1)+1) % 1) as D
,count(*) as N
,t.percentile
,a.gppraccode
into ##mgw_percentileHolding
from ##mgw as a join @t as t on 1=1
group by t.percentile,a.gppraccode

--fold it all over in a manual-ish pivot
select
gppraccode
,max(case when percentile=0 then PercentileValue end) as [0%]
,max(case when percentile=0.25 then PercentileValue end) as [25%]
,max(case when percentile=0.5 then PercentileValue end) as [50%]
,max(case when percentile=0.75 then PercentileValue end) as [75%]
,max(case when percentile=0.8 then PercentileValue end) as [80%]
,max(case when percentile=1 then PercentileValue end) as [100%]
,N
FROM
(
SELECT
*
,(SELECT max(age) from ##mgw as b where b.rownumber<=a.act and a.gppraccode=b.gppraccode) as below
,(SELECT min(age) from ##mgw as b where b.rownumber>=a.act  and a.gppraccode=b.gppraccode) as above
,(SELECT min(age) from ##mgw as b where b.rownumber>=a.act  and a.gppraccode=b.gppraccode)-(select max(age) from ##mgw as b where b.rownumber<=a.act and a.gppraccode=b.gppraccode ) as f
,case	when percentile=0 then (SELECT min(age) from ##mgw as b where a.gppraccode=b.gppraccode)
when percentile=1 then (SELECT max(age) from ##mgw as b where a.gppraccode=b.gppraccode)
else (SELECT max(age) from ##mgw as b where b.rownumber<=a.act and a.gppraccode=b.gppraccode)+D*((select min(age) from ##mgw as b where b.rownumber>=a.act and a.gppraccode=b.gppraccode)-(select max(age) from ##mgw as b where b.rownumber<=a.act and a.gppraccode=b.gppraccode)) end as PercentileValue
FROM ##mgw_percentileHolding as a
) as internal
group by gppraccode,N
```