Calculating Percentage Change

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