Percentiles - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Percentiles

Author

Topic

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-06-01 : 12:54:44

I'm trying to find the 99.6th percentile of the LossAmount column given the following data:

LossAmount Seq
6087 1
2605 2
2308 3
2278 4
1770 so on
1060
714
-3039
-3954
-6955
-7522
-8637
-9065
-11582
-13349
-14379
-17526
-17730 18
-21084 19

So far, I am creating a procedure with the @VaR parameter and would like to use that to calculate the LossAmount value at that percentile. If anyone could help me out, I would really appreciate it! Thanks!

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-06-01 : 14:43:31

For the 99.6th percentile, I should be getting 6087 as my value.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-02 : 07:31:40

The following procedure might help:


--Creating Table

Create Table Ex
(LossAmount int,
 Seq int Identity(1,1) )


--Inserting Sample Data

Insert Into Ex
Select 6087
Union ALL
Select 2605
Union ALL
Select 2308
Union ALL
Select 2278
Union ALL
Select 1770
Union ALL
Select 1060
Union ALL
Select 714
Union ALL
Select -3039
Union ALL
Select -3954
Union ALL
Select -6955
Union ALL
Select -7522
Union ALL
Select -8637
Union ALL
Select -9065
Union ALL
Select -11582
Union ALL
Select -13349
Union ALL
Select -14379
Union ALL
Select -17526
Union ALL
Select -17730
Union ALL
Select -21084


--Procedure For your Requirement

Create Procedure dbo.PercentileCalculator
@Percentile float
As
Begin
Declare @count int = (Select COUNT(*) From Ex)
Declare @PerValue Float
Set @PerValue = Round( ((@Percentile/100) * @count), 0)
;With CTE
As
(Select LossAmount, ROW_NUMBER() Over (Order By LossAmount) As rn From Ex)
Select * From CTE
Where rn = @PerValue
End


--Executing procedure

Execute dbo.PercentileCalculator 99.60

N 28° 33' 11.93148"
E 77° 14' 33.66384"

CJackson
Starting Member

2 Posts

Posted - 2012-06-02 : 15:50:57

Another alternative is this:

DECLARE @Percentile float = 99.6;

WITH X AS
(
SELECT TOP (@Percentile) PERCENT *
FROM EX
ORDER BY LossAmount
)
SELECT TOP 1 *
FROM X
ORDER BY LossAmount DESC

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-06-04 : 11:45:19

Thank you for both of your responses!
Is there any way I can set the LossAmount value to a parameter? I need to reference this value in a later calculation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources