Technical Article

Calculating Percentage Change

,

To provide data analysis you may need to perform some basic trend analysis. For example, if you want to identify the percentage of change in the number of orders received from one month to the next. The challenge to providing that on the fly is the change can be either an increase or a decrease, you can risk divide by zero errors, or your users may need a general percentage regardless of direction.

I wrote this function (SQL 2000) to handle the various scenarios I was seeing, and used it in a couple select statements that ran aggregations to return sum values. To give an example using Northwind, this query will return the total orders per month:

SELECT Count(*), Month(OrderDate)
FROM Northwind..Orders
GROUP BY Month(orderDate)
ORDER BY Month(orderDate)

January has 88 orders and February 83, a decrease in sales by -5.68%. Other way around it would have been an increase by 6.02%. If you didn't care about the direction and only wanted the absolute change it would be an average of 5.85%. This function will let you retrieve any of those values.

SELECT
dbo.PercentageChange (a.prev, b.curr, 0, 0), -- -5.86%
dbo.PercentageChange (b.curr, a.prev, 0, 0), -- 6.02%
dbo.PercentageChange (a.prev, b.curr, 1, 0) -- 5.85%
FROM (SELECT Count(*) as [prev] FROM Northwind..Orders WHERE Month(OrderDate) = 1) as [a],
(SELECT Count(*) as [curr] FROM Northwind..Orders WHERE Month(OrderDate) = 2) as

Any suggestions for improvement are welcome. You can certainly embed the math directly in a sql query and execute faster, the value here is mainly encapsulating the logic and reuse. I hope you find it handy.

Cheers!

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PercentageChange]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[PercentageChange]

GO
CREATE FUNCTION dbo.PercentageChange 
(
    @p_one float, 
    @p_two float, 
    @p_arcChange bit = 0, 
    @p_whole bit = 0
)
/******************************************************************************
**      Name: PercentageChange
**      Desc: Calculate the percentage of change between two values
**              
**      Input Parameters:
**          @p_one - first value, presumably the original
**          @p_two - second value, presumably the changed
**          @p_arcChange - specify whether the change should be directional or absolute
**          @p_whole - specify whether the result should be rounded off and returned without decimals
**
**      Output:
**          @v_Change:  the percentage of change
**
**      Note:
**          "Arc Change" represents approximate change and ignores direction.
**          The sign of the number must be handled when returning a whole number.
**
**      Auth: Ryan Brochez
**      Date: Jan-2007
*******************************************************************************/RETURNS float
AS
BEGIN

    DECLARE @v_Change float

    -- case #1 - no change
    IF @p_one = @p_two 
        SELECT @v_Change = 0.0

    -- case #2 - 100% change
    ELSE IF IsNull(@p_one, 0) = 0 AND IsNull(@p_two, 0) <> 0
        SELECT @v_Change = 100.0

    ELSE IF IsNull(@p_one, 0) <> 0 AND IsNull(@p_two, 0) = 0
    BEGIN
        IF @p_arcChange <> 0 SELECT @v_Change = 100.0
        ELSE SELECT @v_Change = -100.0
    END

    -- case #3 - arc change
    ELSE IF @p_arcChange <> 0
        SELECT @v_Change = Abs((@p_one - @p_two) / ((@p_one + @p_two) * 0.5) * 100)

    -- case #4 - directional change
    ELSE
        SELECT @v_Change = (((@p_two - @p_one) / @p_one) * 100)

    IF @p_whole <> 0 
    BEGIN
        IF @v_Change < 0 SELECT @v_Change = Cast((@v_Change - 0.5) as int)
        ELSE SELECT @v_Change = Cast((@v_Change + 0.5) as int)
    END

    RETURN @v_Change

END
GO

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating