Post reply

calculate percentile

  • June 13, 2017 at 10:37 am

    #343240

    I wanted to calculate the percentile Column from the given data in tsql.
    Here is the data , how can i get % column?

    CurrentDate|PriorDate|7DayAvg|14DayAvg|Date-2|Calculation For percentile|
    607|480|524|506|590  |Percentage between Currentdate and Priordate|
    395|395|395|403|395|Percentage between Currentdate and Priordate|
    0|48|48|48|34|Since current day value is zero , calculate % between priordate and Date-2|
    506|0|345|497|506|Since prior date value is 0 the % between current date and date-2|

  • June 13, 2017 at 11:50 am

    #1946617

    Please post DDL and inserts, what you've tried so far and expected result (requirements). Also check BOL first.

  • June 13, 2017 at 12:50 pm

    #1946633

    CREATE TABLE Dbo.Percentagecalculation
    (

    Currentdate int
    ,PriorDate int
    ,[7DayAvg] Int
    ,[14dayavg] Int
    ,[Date-2] int)

    INSERT iNTO Dbo.Percentagecalculation VALUES(607     ,480     ,524     ,506     ,590)
    GO
    INSERT iNTO Dbo.Percentagecalculation VALUES(395     ,95     ,395     ,403     ,395)
    GO
    INSERT iNTO Dbo.Percentagecalculation VALUES(0     ,48     ,48     ,48     ,34)
    GO
    INSERT iNTO Dbo.Percentagecalculation VALUES(506     ,0     ,345     ,497     ,506)

  • June 13, 2017 at 1:17 pm

    #1946636

    Select *,CASE WHEN Currentdate = 0 then (PriorDate * 100.0/ [Date-2])
        ELSE 
                    CASE WHEN PriorDate=0 THEN (Currentdate * 100.0 / [Date-2])
            ELSE (Currentdate * 100.0 / PriorDate)
                    END
                    END AS [Percent]
             From Dbo.Percentagecalculation

    This is what i did  but % shows 415 ,126....does not seem to be right?

  • June 13, 2017 at 1:19 pm

    #1946637

    Is this what you need?

    CREATE TABLE #Percentagecalculation
           (
            Currentdate numeric(18, 10)
           ,PriorDate numeric(18, 10)
           ,[7DayAvg] int
           ,[14dayavg] int
           ,[Date-2] numeric(18, 10)
           );
    INSERT INTO #Percentagecalculation
        VALUES
            (607, 480, 524, 506, 590)
     ,      (395, 95, 395, 403, 395)
     ,      (0, 48, 48, 48, 34)
     ,      (506, 0, 345, 497, 506);
    SELECT
            p.Currentdate
        ,   p.PriorDate
        ,   p.[7DayAvg]
        ,   p.[14dayavg]
        ,   p.[Date-2]
        ,   CASE WHEN p.Currentdate <> 0 AND
                      p.PriorDate <> 0 THEN p.PriorDate / p.Currentdate
                 ELSE CASE WHEN p.[Date-2] <> 0 AND
                                p.PriorDate <> 0 THEN p.PriorDate / p.[Date-2]
                           ELSE 0
                      END
            END * 100 [%]
        FROM
            #Percentagecalculation p;
    DROP TABLE #Percentagecalculation;


    I changed the data types instead of casting for brevity.

  • June 13, 2017 at 1:23 pm

    #1946640

    the % for most of them shows 0 ...if it is negative needs to show negative ......normally is this right way to calculate the %varience?

  • June 21, 2017 at 6:46 am

    #1947541

    komal145 - Tuesday, June 13, 2017 1:23 PM

    the % for most of them shows 0 ...if it is negative needs to show negative ......normally is this right way to calculate the %varience?

    What Joe posted wasn't quite correct.   Here's the variance percentage:

    CREATE TABLE #Percentagecalculation (
        Currentdate numeric(18, 10),
        PriorDate numeric(18, 10),
        [7DayAvg] int,
        [14dayavg] int,
        [Date-2] numeric(18, 10)
    );
    INSERT INTO #Percentagecalculation (Currentdate, PriorDate, [7DayAvg], [14dayavg], [Date-2])
        VALUES    (607, 480, 524, 506, 590),
                (395, 95, 395, 403, 395),
                (0, 48, 48, 48, 34),
                (506, 0, 345, 497, 506);

    SELECT    p.Currentdate
        ,    p.PriorDate
        ,    p.[7DayAvg]
        ,    p.[14dayavg]
        ,    p.[Date-2]
        ,    ISNULL(CAST(CASE
                            WHEN p.PriorDate = 0 THEN NULL
                            WHEN p.Currentdate <> 0 THEN (p.Currentdate - p.PriorDate) / p.PriorDate
                            WHEN p.[Date-2] <> 0 THEN (p.[Date-2] - p.PriorDate) / p.PriorDate
                            ELSE NULL
                        END * 100. AS varchar(20)), 'INFINITE') + '%' AS [% Variance]
    FROM #Percentagecalculation AS p;

    DROP TABLE #Percentagecalculation;

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • June 21, 2017 at 7:00 am

    #1947546

    My previous post needed a few edits to the query to get the correct results.  FYI, in case you saw it in a previous version.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 8 posts - 1 through 7 (of 7 total)

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