Post reply

TSQL, Percentile with NTILE(100) for less then 100 obesrvations, how to display on 100 scale?

  • September 15, 2015 at 5:08 pm

    #304510

    Hi, all

    I have process and people want to know percentile rank (on scale of 100), I'm bit lost as I have cases when I have let say 5 observations, so if use code below I can not got above number of observations:

    Is there any way to get that numbers like RankWanted ??

    ; WITH cte AS (

    SELECT 100 Cust_ID , 0.338129 Perc, 20 RankWanted union

    SELECT 300 Cust_ID , 0.487179 Perc, 40 RankWanted union

    SELECT 300 Cust_ID, 0.500000 Perc, 60 RankWanted union

    SELECT 400 Cust_ID, 0.541666 Perc, 80 RankWanted union

    SELECT 500 Cust_ID, 0.548780 Perc, 100 RankWanted )

    SELECT Cust_ID, Perc,

    NTILE(5) OVER ( ORDER BY perc ASC) AS PNTILE5,

    NTILE(100) OVER ( ORDER BY perc ASC) AS PNTILE,

    '-' [x], cte.RankWanted

    FROM cte

    Thanks all

    Mario

  • September 16, 2015 at 5:21 am

    #1827018

    Does this do what you're after? Checks if there are less than 100 observations and then multiples the rank by a factor if needed

    USE tempdb;

    WITH CTE

    AS

    (

    SELECT 100 Cust_ID , 0.338129 Perc, 20 RankWanted UNION ALL

    SELECT 300 Cust_ID , 0.487179 Perc, 40 RankWanted UNION ALL

    SELECT 300 Cust_ID, 0.500000 Perc, 60 RankWanted UNION ALL

    SELECT 400 Cust_ID, 0.541666 Perc, 80 RankWanted UNION ALL

    SELECT 500 Cust_ID, 0.548780 Perc, 100 RankWanted

    )

    SELECTC.Cust_ID,

    C.Perc,

    PNTILE5 = NTILE(5) OVER ( ORDER BY C.perc ASC),

    PNTILE = NTILE(100) OVER ( ORDER BY C.perc ASC),

    X = '-',

    C.RankWanted,

    NewRankCalc = NTILE(100) OVER ( ORDER BY perc ASC) * CASE WHEN COUNT(*) OVER (PARTITION BY (SELECT NULL)) < 100 THEN 100 / COUNT(*) OVER (PARTITION BY (SELECT NULL)) ELSE 1 END

    FROMCTE AS C;

  • September 16, 2015 at 9:21 am

    #1827114

    Tx Much, Dohsan!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply