[dba-SQLServer] Creating a Manual TrendLine

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