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