Average of current and previous record Field

  • 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

  • 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/

  • 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