CODEWITHSUNDEEP
sql-serversql-server-2012calculated-columns
Madhukar
Madhukar

Reputation: 11

SQL Server 2012 Computed column

ID  Date    Value   Average
1   10/5/2017   15  15
2   10/6/2017   25  20
3   10/7/2017   35  25
4   10/8/2017   45  35
5   10/9/2017   55  45
6   10/10/2017  65  55
7   10/11/2017  75  65

If this is my table, I want average to be a computed column and its formula in general is average of previous 3 row's Value column. 

(Ex. for 2nd row it is (25+15)/2 )

How can i do such a thing in computed column? Is there any better way to achieve this.

Thanks in advance.

Upvotes: 1

Views: 68

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28940

i would go with a view and use avg windows function 

 select 
    id,
    date,
    value,
    avg(value) over (order by id)
    from table

Updated answer: you could use frames clause like below

Working Demo

;with cte(id,date,val)
as
(

select 1   ,'10/5/2017'  ,  15  UNION ALL
select 2   ,'10/6/2017'  ,  25  UNION ALL
select 3   ,'10/7/2017'  ,  35  UNION ALL
select 4   ,'10/8/2017'  ,  45  UNION ALL
select 5   ,'10/9/2017'  ,  55  UNION ALL
select 6   ,'10/10/2017',  65  UNION ALL
select 7   ,'10/11/2017',  75  
)
SELECT *,avg(VAL) OVER (ORDER BY id rows between 2 PRECEDING  and current row ) FROM CTE

Upvotes: 2

Related Questions