calculate difference and percentage

• sindhupavani123

  Right there with Babe

  Points: 754

  More actions

  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

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  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

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  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

• sindhupavani123

  Right there with Babe

  Points: 754

  More actions

  November 17, 2016 at 7:32 am

  #1913064

  Thanks very much!