Average of current and previous record - Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Average of current and previous record - Column

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-04-12 : 06:58:29

Good day everybody,

Nice to be in this forum, hope I find help for my problem in asp.net/sql2000.

Because I can't create trend-lines with average of last 2 points in MSChart, I need to build a query that can do it. That's because my (power consumption) chart is fluctuating to fast to get a nice curve.

So I got this table and need this extra avg-column in my view/query.
That average column displays the average of the current and previous value.

tbl_5min_kwh

id time c avg
1 10:00 4 4 (first record)
2 10:05 6 5
3 10:10 3 4,5
4 10:15 7 5
5 10:20 8 7,5
6 10:25 2 5
7 10:30 4 3

That average column would come in my chart time-average.
It would be usefull to be able to set the averaging number (in this case 2). But thats details...

It's also possible to run a stored procedure over the table, don't now whats easiest.

I tried and searched the web but found nothing :(
Who can take this challange?

Thanx in advance! Greetings, djorre

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-04-12 : 10:58:26

Assuming that ID is the column used to determine what the 'previous row' is, try something like this:

SELECT < Columns > FROM tbl_5min_kwh current LEFT OUTER JOIN tbl_5min_kwh previous ON current.ID = Previous.ID + 1

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-12 : 12:23:39

See Moving Average here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-12 : 13:18:21

[code]SELECT t.id,t.time,t.c,
COALESCE(t1.RunAvg,0) AS avg
FROM Table t
OUTER APPLY (SELECT AVG(c*1.0) AS RunAvg
FROM Table
WHERE id <=t.id) t1
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-12 : 13:59:12

Visakh, only the average of current and previous record, not all record until current.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-12 : 14:08:49

[code]SELECT t.id,t.time,t.c,
(COALESCE(t1.c,0)+t.c)/2 AS avg
FROM Table t
OUTER APPLY (SELECT TOP 1 c
FROM Table
WHERE id <t.id
ORDER BY id DESC) t1
[/code]

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-13 : 00:58:37

Hi, try this query

CREATE TABLE temp (ID INT, tm datetime, VAL int)

INSERT INTO temp VALUES(1, '04/12/2009 10:00' , 4)
INSERT INTO temp VALUES(2, '04/12/2009 10:05' , 6)
INSERT INTO temp VALUES(3, '04/12/2009 10:10' , 3)

INSERT INTO temp VALUES(4, '04/12/2009 10:15' , 7)
INSERT INTO temp VALUES(5, '04/12/2009 10:20' , 8)
INSERT INTO temp VALUES(6, '04/12/2009 10:25' , 2)

INSERT INTO temp VALUES(7, '04/12/2009 10:30' , 4)

select id,tm,val,(select round(avg(cast(val as float)),2) from temp t where t.id in (t1.id,t1.id-1)) avg from temp t1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources