Post reply

calculate difference and percentage

  • November 17, 2016 at 7:01 am

    #401073

    Hi,

    I have following data , numbers recruited/applied for a particular office, and would like to find the difference between previous and current year and their percentage increase or decrease.

    Mentioned the formulas to use in brackets in expected output.

    Office year recruited applied

    Pune 2015 10 15

    Pune 2016 7 20

    Mumbai 2015 10 23

    Mumbai 2016 15 18

    My expected output should be like:

    Office Difference %recruited

    Pune -3 (7-10) -30%(7-10/10)

    Mumbai 5(15-10) 50%

    Please help.

    Thanks

  • November 17, 2016 at 7:07 am

    #1913050

    sindhupavani123 (11/17/2016)

    Hi,

    I have following data , numbers recruited/applied for a particular office, and would like to find the difference between previous and current year and their percentage increase or decrease.

    Mentioned the formulas to use in brackets in expected output.

    Office year recruited applied

    Pune 2015 10 15

    Pune 2016 7 20

    Mumbai 2015 10 23

    Mumbai 2016 15 18

    My expected output should be like:

    Office Difference %recruited

    Pune -3 (7-10) -30%(7-10/10)

    Mumbai 5(15-10) 50%

    Please help.

    Thanks

    1) To get better help, please always provide a create table statement, inserts with your sample data and then the output (which you did).

    2) Since you are on 2012, go look up lag and lead which were part of the awesome windowing function enhancements released in that version.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • November 17, 2016 at 7:11 am

    #1913054

    See if this helps

    DECLARE @myTable TABLE(Office VARCHAR(10), yr INT, recruited int, applied INT)

    INSERT INTO @myTable(Office, yr, recruited, applied)

    VALUES('Pune', 2015, 10, 15),

    ('Pune', 2016, 7, 20),

    ('Mumbai', 2015, 10, 23),

    ('Mumbai', 2016, 15, 18);

    SELECT currYear.Office,

    currYear.yr,

    currYear.recruited - prevYear.recruited AS Difference,

    (100 * (currYear.recruited - prevYear.recruited))/NULLIF(prevYear.recruited,0) AS [%recruited]

    FROM @myTable currYear

    INNER JOIN @myTable prevYear ON currYear.office = prevYear.office AND currYear.yr = prevYear.yr + 1;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • November 17, 2016 at 7:32 am

    #1913064

    Thanks very much!

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

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