Jun 24

Written by: Steve Gray
6/24/2010 11:32 AM 

The task here is to get a ‘percent changed’ figure in SQL. The code sample below shows how to achieve this. The trick is going to be to add a row number, and then to get a reference to the preceding row.

SQL 2005 gives us the ROW_NUMBER() function which will handle this nicely.

Armed with that reference you can drop in a math function to format the ‘percent changed’ any way you need.

declare @custSummary as table (customer varchar(15), year1  int, sales  numeric(19,2))
insert into @custSummary (customer,year1,sales) values ('cust01',2000,20121)
insert into @custSummary (customer,year1,sales) values ('cust01',2001,22234)
insert into @custSummary (customer,year1,sales) values ('cust01',2002,18765)
insert into @custSummary (customer,year1,sales) values ('cust01',2003,17345)
insert into @custSummary (customer,year1,sales) values ('cust01',2004,21234)
drop table #tmp
--run the custSummary table into a temp table, and add a RowID
select customer,year1,sales, row_number() over(order by year1) as rowid
    into #tmp
    from @custSummary
    order by year1
    
--now add a reference to the preceding row and compute percent changed    
select t.customer,t.year1,t.rowid ,t.sales, t2.sales, isnull(t2.sales,1) / t.sales 
    from #tmp t
        left join #tmp t2 on t2.rowid = t.rowid - 1    
    order by t.rowid
    
    

 

 

This will return this: