sql server 2008 - calculate difference and percentage -

i have following data , numbers recruited/applied particular office, , find difference between previous , current year , percentage increase or decrease.

mentioned formulas 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 like:

office      difference      %recruited pune        -3  (7-10)      -30%(7-10/10) mumbai      5(15-10)         50% 

please help.

if using sql server 2012 or higher use lag (or lead) function. since aren't can creative cte. approach taken http://blog.sqlauthority.com/2013/09/22/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement/.

select 'pune' office, '2015' year, 10 recruited, 15 applied #temp union  select 'pune' office, '2016' year, 7 recruited, 20 applied union  select 'mumbai' office, '2015' year, 10 recruited, 23 applied union  select 'mumbai' office, '2016' year, 15 recruited, 18 applied;  cte ( select rownum = row_number() on (partition t.office order t.year), * #temp t) select cte.office, cte.recruited - prv.recruited differencerecruited, ((cte.recruited - prv.recruited) / convert(float, prv.recruited) * 100) recruitedchangepercentage, cte.applied - prv.applied differenceapplied, ((cte.applied - prv.applied) / convert(float, prv.applied) * 100) appliedchangepercentage   cte left join cte prv on prv.office = cte.office , prv.rownum = cte.rownum - 1 prv.recruited not null order cte.office desc 

hope helps.