Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Average of current and previous record Field Expand / Collapse
Author
Message
Posted Sunday, April 12, 2009 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 7:46 AM
Points: 2, Visits: 10
Good day everybody,

Nice to be in this forum, hope I find help for my problem in ASP.NET/T-SQL.

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
Post #695415
Posted Sunday, April 12, 2009 5:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 2,122, Visits: 5,476
Pleas use the URL in my signature and have a look how to post a question in a better way. As for your question. According to the data that you gave us (id with no gaps, need the average of 2 records) you can do it this way:
create table Demo (id int, Time datetime, value numeric(3,1))
go

insert into demo (id, time, value)
select 1, '10:00:00', 4
union select 2, '10:05:00', 6
union select 3, '10:10:00', 3
union select 4, '10:15:00', 7
union select 5, '10:20:00', 8
union select 6, '10:25:00', 2
union select 7, '10:30:00', 4
go


select a.id, a.time, a.value, (a.value + isnull(b.value, a.value))/2 as AvgCurrentAndPref
from demo as a left join demo as B on a.id = b.id+1

go
drop table demo

Notice that if you have gaps in the ID column, you can use common table expression with ranking functions instead of the ID column.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #695420
Posted Sunday, April 12, 2009 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 7:46 AM
Points: 2, Visits: 10
Thank you VERY MUCH for the fast response! It works! Great!
Post #695467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse