Creating a Scalar-valued function

Question

Perhaps this approach itself is not correct, but, here is what I did and what I am trying to achieve.

I have a series that looks like this:

value
1.0000
2.0000
3.0000
4.0000
5.0000
6.0000
7.0000
8.0000
9.0000
10.0000

I want to add a growth rate to it as a column. So I made a scalar-value function, which is not working as I thought it would. It returns NULL where I thought it would return a number.

Here is my function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[ts_growth_rate] (
    @x NUMERIC(28, 10),
    @scale NUMERIC(28, 10) = 100,
    @power NUMERIC(28, 10) = 1,
    @log_diff BIT = 0
    )
RETURNS NUMERIC(28, 10)
AS
BEGIN
    DECLARE @growth_rate NUMERIC(28, 10);

    SELECT @growth_rate = CASE 
            WHEN @log_diff = 1
                THEN CASE 
                        WHEN LAG(@x) OVER (ORDER BY @x) IS NOT NULL
                            THEN LOG(@x / LAG(@x) OVER (ORDER BY @x)) * @scale
                        ELSE NULL
                        END
            ELSE CASE 
                    WHEN LAG(@x) OVER (ORDER BY @x) IS NOT NULL
                        THEN ((POWER(@x / LAG(@x) OVER (ORDER BY @x), @power) - 1) * @scale)
                    ELSE NULL
                    END
            END;

    RETURN @growth_rate;
END;

Here is what gets returned in a query, I have a case expression that produces what I want the function to return.

declare @x NUMERIC(28, 10);
declare @scale NUMERIC(28, 10) = 100;
declare @power NUMERIC(28, 10) = 1;
declare @log_diff BIT = 1;

select value,
    [gr] = dbo.ts_growth_rate(value, 100, 1, 0),
    [gr2] = CASE 
            WHEN @log_diff = 1
                THEN CASE 
                        WHEN LAG(value) OVER (ORDER BY value) IS NOT NULL
                            THEN LOG(value / LAG(value) OVER (ORDER BY value)) * @scale
                        ELSE NULL
                        END
            ELSE CASE 
                    WHEN LAG(value) OVER (ORDER BY value) IS NOT NULL
                        THEN ((POWER(value / LAG(value) OVER (ORDER BY value), @power) - 1) * @scale)
                    ELSE NULL
                    END
            END 
from #tempa

value   gr  gr2
1.0000  NULL    NULL
2.0000  NULL    69.3147180559945
3.0000  NULL    40.5465108108164
4.0000  NULL    28.7682072451781
5.0000  NULL    22.314355131421
6.0000  NULL    18.2321556793955
7.0000  NULL    15.4150679827258
8.0000  NULL    13.3531392624522
9.0000  NULL    11.7783035656383
10.0000 NULL    10.5360515657826

Answer 1

You built your function to look at LAG() inside itself, in which case is not known to the scalar function. Think of the scalar function as being it's own environment, it takes what you give it for data, and returns based solely on the data you gave it. It's returning NULL because a LAG() on nothing is NULL and anything multiplied by NULL = NULL.

LAG() is an analytic function an not a scalar or table valued, in which are the only options for custom functions. This, of course, is leaving out the conversation of CLRs.

The only way your function can know the lag value is if you pass it the lag value:

ALTER FUNCTION [dbo].[ts_growth_rate] (
    @x NUMERIC(28, 10),
    @scale NUMERIC(28, 10) = 100,
    @power NUMERIC(28, 10) = 1,
    @log_diff BIT = 0,
    @lag NUMERIC(28, 10) = NULL
    )
RETURNS NUMERIC(28, 10)
AS
BEGIN
    DECLARE @growth_rate NUMERIC(28, 10);

    SELECT @growth_rate = CASE 
            WHEN @log_diff = 1
                THEN CASE 
                        WHEN @lag IS NOT NULL
                            THEN LOG(@x / @lag) * @scale
                        ELSE NULL
                        END
            ELSE CASE 
                    WHEN @lag IS NOT NULL
                        THEN ((POWER(@x / @lag, @power) - 1) * @scale)
                    ELSE NULL
                    END
            END;

    RETURN @growth_rate;
END;

Then be sure to pass the @lag value from your query:

select 
    value,
    [gr] = dbo.ts_growth_rate(value, 100, 1, 0, LAG(value) OVER (ORDER BY value))
from #tempa