I would like to ask for some help and maybe the best practices on calculating the 90th percentile using TSQL. To be clear after searching online i'm looking for one value to show based on various numbers during a time frame. Example what is the 90th percentile for students duration of an exam for the week of July 1st.
I believe i have figured out the formula below (which excludes NULLS)
select max(case when rownum*1.0/numrows <= 0.9 then <column> end) as percentile_90th
from (select <column>,
row_number() over (order by <column>
count(*) over (partition by NULL) as numrows
where <column> is not null AND DATE = '12-17-2012' (etc)
There are now 2 items I’m still working on to complete this query at my workplace and that is:
1 – get 2 decimal places as result (right now the output has no decimal places)
2 – integrate the Date to be dynamic (for last week for example) -> replace static with DATE >= CONVERT(date, GETDATE() -8)
Before i go ahead and spend more time i wanted to see what the community has for the best method to calculate percentile? i did a spot check and extracted the raw data and found this calculation is right on except for 1 date range i used and i'm not sure why right now.
Any advice is appreciated!