title: MS SQL - User Defined Function - Slope Intercept RSquare ; How to Group by Portfolio
body: <p>Below is an example of my data, table RR_Linest:</p>
  
  <p>Portfolio   ----         Month_number      ----           Collections<br>
  A               ---       ---------      1    ---------------------                          $100-------------------------------------------------------------------------------------
  A--------------                            2    ---------------------                        $90<br>
  A                         -------------   3       ---------------------                     $80--------------------------------------------------------------------------------------
  A                    -------------        4      ---------------------                      $70--------------------------------------------------------------------------------------
  B  -------------                          1    --------------------                        $100-------------------------------------------------------------------------------------
  B            ----        --------        2     ----------------------                       $90  -------------------------------------------------------------------------------------
  B                  -      ------------    3                         ---------------------   $80</p>
  
  <p>I was able to figure out how to how to get the slope,intercept, RSquare for one portfolio by removing the portfolio column and only selecting the month_Number (x) and collections data (y) for only <strong>one selected portfolio</strong> (I removed data for portfolio B) and running the code below.</p>
  
  <p><strong>I have been trying to change the function so that when I run it; it gives me the slope, intercept, and R-square by portfolio.  Does someone know how to do that?  I have tried many ways and I just can't figure it out.</strong></p>
  
  <p><strong>First I created the function:</strong></p>
  
  <p>declare @RegressionInput_A  [dbo].[RegressionInput_A]</p>
  
  <p>insert into @RegressionInput_A (x,y)
  select
  ([model month]),log([collection $])
  from [dbo].[RR_Linest]</p>
  
  <p>select * from [dbo].LinearRegression_A</p>
  
  <pre><code>GO
  
  drop function dbo.LinearRegression_A
  
  CREATE FUNCTION dbo.LinearRegression_A
  ( 
  @RegressionInputs_A AS dbo.RegressionInput_A READONLY 
  ) 
  RETURNS @RegressionOutput_A TABLE 
  ( 
  Slope DECIMAL(18, 6), 
  Intercept DECIMAL(18, 6), 
  RSquare DECIMAL(18, 6) 
  ) 
  AS
  BEGIN 
  
  DECLARE @Xaverage AS DECIMAL(18, 6)
  DECLARE @Yaverage AS DECIMAL(18, 6)
  DECLARE @slope AS DECIMAL(18, 6)
  DECLARE @intercept AS DECIMAL(18, 6)
  DECLARE @rSquare AS DECIMAL(18, 6)
  
  SELECT
  @Xaverage = AVG(x),
  @Yaverage = AVG(y)
  FROM
  @RegressionInputs_A
  
  SELECT
  @slope = SUM((x - @Xaverage) * (y - @Yaverage))/SUM(POWER(x - @Xaverage, 2))
  FROM
  @RegressionInputs_A
  
  SELECT
  @intercept = @Yaverage - (@slope * @Xaverage) 
  
  SELECT @rSquare = 1 - (SUM(POWER(y - (@intercept + @slope * x), 2))/(SUM(POWER(y - (@intercept + @slope * x), 2)) + SUM(POWER(((@intercept + @slope * x) - @Yaverage), 2))))
  FROM
  @RegressionInputs_A
  
  INSERT INTO
  @RegressionOutput_A
  (
  Slope,
  Intercept,
  RSquare
  )
  SELECT
  @slope,
  @intercept,
  @rSquare
  
  RETURN
  
  END
  GO
  </code></pre>
  
  <p><strong>Then I run the function</strong></p>
  
  <pre><code>declare @RegressionInput_A  [dbo].[RegressionInput_A]
  
  insert into @RegressionInput_A (x,y)
  select
  ([model month]),log([collection $])
  from [dbo].[RR_Linest]
  
  select * from [dbo].[LinearRegression_A](@RegressionInput_A)
  </code></pre>