95th percentile calculation

Hi *,

For our needs, we have to integrate on a Visual Studio project (a Report Server Project, which gives a report dealing with AVG, MAX counters' values from a SQL DB), a calculation of a '95th percentile' average for a counter (in place of a 'normal' average).
(In another words, we have to automatically substract the 5th 'highest values' of the total of the value, and so only make the average on the remaining 95th of the value).
And this treatment should appear as a function, that we could integrate on the Report.

Does this function already exist ?

Does anyone have any idea to help me to find how to do ?

Tks a lot to everybody.

HI,snakeoli :

Can you elaborate more about your requirment? Do you need something like top 5 percent?

|||

Hi,

No, in fact, we have to calculate the average, in the case for several ranges of values.
But to have 'relevant' results, for each sorted ranges of values, we have to exclude 5% of the highest values before calculating the average.
This maneer is called '95th percentile calculation'.

(for ex : in this range of 20 values : 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
the 95th percentile give : 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 !!
=> exclusion of the 5thhighest values of the list (5% of '20' gives '1' ==> 1 value)
And so, calculation of the average only on 19 values !)

The goal is, in some case, to supress values that can influence the result of a average.

Imagine that in this example, we have '1250' in the place of '20' !

So the average of the 20 values gives a result not relevant !
(1+ 2+ 3+ 4 + ...+ 18 + 19 + 1250) / 20

And so, finaly, that what we need to create, throught Visual Studio :
a function (similar as 'AVG()') that can calculate automaticaly this !
(for a sorted range of value, a average excluding the 5th highest values !)

I whisper sincerely that I was clearly in my explanation.

Thanks a lot for your help.

|||

Hi,snakeoli:

I got to know what you want to do in this scenario. However AVG() can not deal with such comlicated calculation for you. I just recommend you to do this in the sql statement. It will be much reasonable.

7088.03 - 7088.03 = -9.09494701772928E-13

I've been having odd problems like this recently.
I will do a calculation on my data and get a value (for example 7088.03) and compare it to a previous value in another table (also 7088.03) and I want the difference of the two but I get a number that is non-zero (in this case: -9.09494701772928E-13) for equivalent values.

Does anyone know what the cause is? I've already checked to make sure the datatypes are the same.

The reason is that you are using float / real data types. These are approximate data types and follow the IEEE 754 specification. So depending on the operation you are doing or arithmetic manipulation it is possible that you get the same value with different precisions. This is expected and by design. If you need exact scale/precision then you should use data type like decimal or numeric. See BOL for more details on using float/real starting with topic below:

http://msdn2.microsoft.com/en-us/library/ms187912.aspx