April 12, 2009 at 5:17 am
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
April 12, 2009 at 5:52 am
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/
April 12, 2009 at 9:29 am
Thank you VERY MUCH for the fast response! It works! Great!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply