How to calculate the average value and update it i

Question

Thank you in advance. I have a Mytable1:

Site_name | date& Time                |PowerOutput         

xyz001    |2013-07-21 01:00:00.000    |192
xzu001    |2013-07-21 02:00:00.000    |189    
abf003    |2013-07-21 03:00:00.000    |0
ACT0001   |2013-07-21 04:00:00.000    |178

I want to calculate average value of the previous row and the next row in case of ZERO.

My output table should be:

Sitename  |date&time                  |Power_output   

xyz001    |2013-07-21 01:00:00.000    |192
xzu001    |2013-07-21 02:00:00.000    |189 
abf003    |2013-07-21 03:00:00.000    |189
ACT0001   |2013-07-21 04:00:00.000    |178

The logic is :

((Previous value-next value)/previous value) *100 <5, If this is true then it should insert the previous value

((Previous value-next value)/previous value)*100 >=5, If this is true then it should remain as zero.

Answer 1

Try This

BEGIN TRAN

DECLARE @Id INT ,@PreValue INT,@NextValue INT
CREATE TABLE #table(_Id INT IDENTITY(1,1) , Site_name VARCHAR(100),_dateTime DATETIME,PowerOutput INT,_upFlg TINYINT DEFAULT(0))        

INSERT INTO #table( Site_name ,_dateTime ,PowerOutput)
SELECT 'xyz001'    ,'2013-07-21 01:00:00.000'   ,192 UNION ALL
SELECT 'xzu001'    ,'2013-07-21 02:00:00.000'    ,189    UNION ALL 
SELECT 'abf003'    ,'2013-07-21 03:00:00.000'    ,0UNION ALL
SELECT 'ACT0001'    ,'2013-07-21 04:00:00.000'    ,178


WHILE EXISTS(SELECT 1 FROM #table WHERE PowerOutput = 0 AND _upFlg = 0)
BEGIN

     SELECT Top 1 @Id = _Id FROM #table WHERE PowerOutput = 0 AND _upFlg = 0

     SELECT @PreValue = PowerOutput FROM #table WHERE _Id = @Id - 1
     SELECT @NextValue = PowerOutput FROM #table WHERE _Id = @Id + 1

     IF  ( (@PreValue-@NextValue)/@PreValue ) < 5
       UPDATE #table SET PowerOutput = @PreValue WHERE _id = @id

     UPDATE #table SET _upFlg = 1 WHERE _Id = @Id

END


SELECT * FROM #table




ROLLBACK TRAN