A loss in profits occurred on employee 301’s watch. We want the percentage change to be negative in that case. There are several different solutions that I can think of and many more I can’t think of. The SIGN function is an obscure and rarely used function that can save our query.

select n.employeeId

, nMinus1.period as period_from

, n.period as period_to

, ABS( ( (n.amount - nMinus1.amount) / nMinus1.amount ) * 100 )

* SIGN(n.amount - nMinus1.amount) as pct_change

from #profit n inner join #profit nMinus1

on n.employeeId = nMinus1.employeeId

where n.period - 1 = nMinus1.period;

employeeId period_from period_to pct_change

----------- ----------- ----------- ----------

101 1 2 100

201 1 2 200

301 1 2 -50

Sqlblog reader Marc Shapiro simplified the query as shown below. It's definitely better. Now I'll have to think of a better example for using the SIGN function!

select n.employeeId

, nMinus1.period as period_from

, n.period as period_to

, ( (n.amount - nMinus1.amount) / ABS(nMinus1.amount) ) * 100 as pct_change

from #profit n inner join #profit nMinus1

on n.employeeId = nMinus1.employeeId

where n.period - 1 = nMinus1.period;

Another reader, Leonid, asked if nMinus1.amount could equal zero. It can if the following inserts are executed.

insert into #profit (employeeId, period, amount) values (401,1, 0);

insert into #profit (employeeId, period, amount) values (401,2,1000);

We all know that division by zero is mathematically undefined, but it demands a business definition in the form of complete requirements. What is the business rule for going from zero profit in one period to the next period? Until that question is answered, the solution cannot be coded. If the rule is to treat the percentage increase as 100%, then the following code would satisfy the requirements:

select n.employeeId

, nMinus1.period as period_from

, n.period as period_to

, case nMinus1.amount

when 0 then 100

else ( (n.amount - nMinus1.amount) / ABS(nMinus1.amount) ) * 100

end as pct_change

from #profit n inner join #profit nMinus1

on n.employeeId = nMinus1.employeeId

where n.period - 1 = nMinus1.period;

Satisfying business requirements may necessitate implementing something other than the obvious solution. It is absolutely essential to understand the business requirements before implementing a solution. Now I will directly state what I was implying by example: Business requirements need to be turned into test cases. The test cases should drive the development process.

## Comments

## MarcShapiro said:

I think the last computation is simpler as

( (n.amount - nMinus1.amount) / ABS(nMinus1.amount) ) * 100

## Leonid said:

Is there a chance that nMinus1.amount=0?