[dba-SQLServer] Creating a Manual TrendLine

MartyConnelly martyconnelly at shaw.ca
Tue Aug 15 18:41:51 CDT 2006


Have a look at this first
How to create a bar graph that has a trend line in a chart in Report 
Designer
http://support.microsoft.com/kb/842422/en-us

Or you could run something like this via UDF or straight SQL
Essentially you would have you X (Date) and Y value
in the table, run the SQL to create a third field in the Table YY
containing interpolated Y value via least square fit method.

Sub sInterpolation()
    Dim strSQL As String
    Dim m As Double
    Dim b As Double
   ' Best least square Fit Line: Y = mX + b
    Call sRegressionLineA(m, b)
    'add new calc Y values to table
    strSQL = "UPDATE Table1 SET Table1.YY = " & _
    "(" & m & ")" & " * [X] + (" & b & ");"
     Debug.Print strSQL
     CurrentDb.Execute strSQL
     Debug.Print "end"
End Sub

Function sRegressionLineA(m As Double, b As Double)
    Dim dbs As Database, rcs As Recordset
    Dim strSQL As String
    strSQL = "SELECT Sum(Table1.X) AS SumX, " & _
    "Sum([X]*[X]) AS SumXX, Sum(Table1.Y) AS SumY, Sum([X]*[Y]) AS 
SumXY, " & _
    "Count(Table1.X) AS N FROM Table1 WHERE (((Table1.X) Is Not Null) 
AND ((Table1.Y) Is Not Null));"
    Debug.Print strSQL
    Set dbs = CurrentDb()
   Set rcs = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    m = (rcs!N * rcs!SumXY - rcs!SumX * rcs!SumY) / (rcs!N * rcs!SumXX - 
rcs!SumX ^ 2)
    b = (rcs!SumY * rcs!SumXX - rcs!SumX * rcs!SumXY) / (rcs!N * 
rcs!SumXX - rcs!SumX ^ 2)
    Debug.Print m; b
End Function

If you had the Excel Solver Pack installed on the server you could call it's
curve fitting functions, easy to do in Access might be a pain in SQL,
haven't tried.



Francisco Tapia wrote:

>Marty thanks for responding.  The data that I have is:
>
>Jan-03		1.06	395
>Feb-03		0.91	256
>Mar-03		1.40	225
>Apr-03		1.23	280
>May-03		1.31	224
>Jun-03		1.33	256
>Jul-03		1.40	305
>Aug-03		1.21	232
>Sep-03		1.10	304
>Oct-03		0.83	293
>Nov-03		0.91	322
>Dec-03		1.21	319
>Jan-04		1.05	222
>
>I want to trend the 2nd column, If i use the standard slope formula, I
>suppose X just represents rows 1, 2, 3, etc.
>
>
>
>On 8/12/06, MartyConnelly <martyconnelly at shaw.ca> wrote:
>  
>
>>There are a lot of equations you can use to curve fit a trendline
>>But sticking with say something simple, say a moving average.
>>
>>Here are two examples from Joe Celko's book SQL for Smarties
>>You can find other examples here.
>>
>>Usually a moving average is calculated over a fixed period of time
>>(i.e. last five days, last two batches, etc.)
>>
>>SELECT B2.mydate, AVG(B1.lData) AS moving_avg_wgt
>>FROM Table1 AS B1, Table1 AS B2
>>WHERE B1.mydate
>>       BETWEEN B2.mydate
>>           AND (B2.mydate - 5)
>>GROUP BY B2.mydate;
>>
>>or if you do not like the scalar subquery expression:
>>
>>SELECT mydate, (SELECT AVG(ldata)
>>          FROM Table1 AS B1
>>         WHERE B1.mydate
>>               BETWEEN B2.mydate
>>                   AND B2.mydate - 1) AS moving_avg_wgt
>>FROM Table1 AS B2;
>>
>>ID    lData    mydate
>>1    10    01/01/2006
>>2    20    02/01/2006
>>3    40    03/01/2006
>>4    390  04/01/2006
>>5    50    05/01/2006
>>6    30    02/01/2006
>>
>>
>>Table1 ID       lData   mydate
>>1       10      01/01/2006
>>2       20      02/01/2006
>>3       40      03/01/2006
>>4       390     04/01/2006
>>5       50      05/01/2006
>>6       30      02/01/2006
>>
>>
>>
>>
>>Francisco Tapia wrote:
>>
>>    
>>
>>>After much re-search I found that I could "NOT" easily just create a
>>>trend line in Sql Server Reporting Services (2000).  Therefore I need
>>>to manually create the data for a TrendLine.  Question is, Does anyone
>>>know how?
>>>
>>>My Data looks like this: (object is to trend the Failure rate)
>>>
>>>Month,   Installs, Fails,  Trend
>>>01/00,   200,  1.5,  ?
>>>01/00,   278,  1.0,  ?
>>>01/00,   325,  .9,  ?
>>>...
>>>...etc
>>>
>>>Thanks,
>>>
>>>
>>>
>>>
>>>
>>>      
>>>
>>--
>>Marty Connelly
>>Victoria, B.C.
>>Canada
>>
>>_______________________________________________
>>dba-SQLServer mailing list
>>dba-SQLServer at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>http://www.databaseadvisors.com
>>
>>
>>    
>>
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada




More information about the dba-SQLServer mailing list