How to calculate different Trend line types in SQL function

Question

i have been seeking a good way to calculate excel like trendlines for my desktop app's dashboard graps.

most common trend types must be like

Type = 1 Linear Y = a + b*X
Type = 2 EXponential Y = a*e^(b*X)   
Type = 3 Logarithmic Y = a + b*ln(X)
Type = 4 Power Y = a*X^b

so here is how i solved my issue.

Answer 1

we need an input type to make it an SQL function

CREATE Type XYTableType 
AS TABLE (X float, Y float)

X can be a datetime if you want to work with time series. i try to make this function as simple as possible

create FUNCTION dbo.fn_TrendLine(@Type TINYINT, @raw_data XYTableType READONLY)
RETURNS @TrendTable TABLE(X float, Y float, Yt float)
AS
BEGIN
    DECLARE @n DECIMAL(38, 10),
        @x DECIMAL(38, 10),
        @x2 DECIMAL(38, 10),
        @y DECIMAL(38, 10),
        @xy DECIMAL(38, 10),
        @y2 DECIMAL(38, 10),
        @a DECIMAL(38, 10),
        @b DECIMAL(38, 10)

    SELECT  
    @n=COUNT(*),
    @x= sum(CASE
            WHEN @Type = 2 THEN X
            WHEN @Type = 3 THEN LOG(X)
            WHEN @Type = 4 THEN LOG(X)
            ELSE X
            END),
    @x2=sum(CASE
            WHEN @Type = 2 THEN X * X
            WHEN @Type = 3 THEN LOG(X) * LOG(X)
            WHEN @Type = 4 THEN LOG(X) * LOG(X)
            ELSE X * X
            END),
    @y= sum(CASE
            WHEN @Type = 2 THEN LOG(Y)
            WHEN @Type = 3 THEN Y
            WHEN @Type = 4 THEN LOG(Y)
            ELSE Y
            END),
    @xy=sum(CASE
            WHEN @Type = 2 THEN X * LOG(Y)
            WHEN @Type = 3 THEN LOG(X) * Y
            WHEN @Type = 4 THEN LOG(X) * LOG(Y)
            ELSE X * Y
            END),
    @y2=sum(CASE
            WHEN @Type = 2 THEN LOG(Y) * LOG(Y)
            WHEN @Type = 3 THEN Y * Y
            WHEN @Type = 4 THEN LOG(Y) * LOG(Y)
            ELSE Y * Y
            END)
    FROM @raw_data
    where Y is not null

    set @a = (@x2 * @y - @x * @xy) / (@n * @x2 - @x * @x)
    set @b = (@n * @xy - @x * @y) / (@n * @x2 - @x * @x )
    if @Type in (2,4)
        set @a = exp(@a)

    INSERT INTO @TrendTable(X, Y, Yt)
    SELECT  X,Y,
        Yt= case 
            WHEN @Type = 2 THEN @a *exp(log(exp(1))*(@b * X))
            WHEN @Type = 3 THEN @a + @b * LOG(X)
            WHEN @Type = 4 THEN @a * exp(log(X)*@b) 
            ELSE @a + @b * X
            end
    from @raw_data

    RETURN
END

here is an example

DECLARE @raw_data XYTableType
insert into @raw_data
values (1,1.15),(2,1.82),(3,3.13),(4,4.28),(5,4.67),(6,5.79),(7,7.81),(8,8.35),(9,9.40),(10,9.98),(11,5.79),(12,7.81),(13,8.35),(14,9.40),(15,null),(16,null)
select * from dbo.fn_TrendLine(1, @raw_data)
select * from dbo.fn_TrendLine(2, @raw_data)
select * from dbo.fn_TrendLine(3, @raw_data)
select * from dbo.fn_TrendLine(4, @raw_data)

x15 and x16 are there for extrapolations. you simply can copy results and paste it into an excel sheet to check with graphs or builtin excel functions.

important note: type 3,4 can't work with negative X values, 2,4 can't work with negative Y values. preparing the data is important before you calculate the certain types.