SELECT
	y1.[date],
	AVG(y2.valueColumn) AS average3days
FROM
	YourTable y1
	CROSS APPLY
	( 
		SELECT TOP (3) y2.valueColumn FROM YourTable y2
		WHERE y2.date >= y1.date
		ORDER BY y2.date
	) y2

UPDATE x1 SET average3days = x2.average3days
FROM
	YourTable x1
	INNER JOIN
	(
	SELECT
		y1.[date],
		AVG(y2.valueColumn) AS average3days
	FROM
		YourTable y1
		CROSS APPLY
		( 
			SELECT TOP (3) y2.valueColumn FROM YourTable y2
			WHERE y2.date >= y1.date
			ORDER BY y2.date
		) y2
	) x2 ON x1.[date] = x2.[date];

.....
		CROSS APPLY
		( 
			SELECT TOP (3) y2.valueColumn FROM YourTable y2
			WHERE y2.date >= y1.date
			ORDER BY y2.date
		) y2
....

		CROSS APPLY
		( 
			SELECT TOP (3) y2.valueColumn FROM YourTable y2
			WHERE y2.date >= y1.date
			  AND dateadd(dd,2,y1.date) <= y2.date
			ORDER BY y2.date
		) y2

UPDATE t
SET average3days = [3DayAvg]
FROM (SELECT average3days,AVG(valueColumn * 1.0) OVER (ORDER BY [date] ROWS BETWEEN CURRENT ROW AND PRECEDING 2) AS [3DayAvg]
      FROM Table
     )t

quote:

---

Originally posted by landau66

hi!
thank you so much. it was exactly what i needed. it took me some days to understand. by the way i am on an SQL-Server 2012

many greetings
landau

---

UPDATE t
SET average3days = [3DayAvg]
FROM (SELECT average3days,AVG(valueColumn * 1.0) OVER (ORDER BY [date] ROWS BETWEEN CURRENT ROW AND PRECEDING 2) AS [3DayAvg]
      FROM Table
     )t

select valueColum, AVG(valueColum) over (order by [date] rows between 2  preceding and current row ) as [3DayAvg]
from DAX

quote:

---

Originally posted by landau66

HI!

I have to come back to this Topic. I thought I understood it but still I have got some Problems with it. The solution of visakh suggests the following:

UPDATE t
SET average3days = [3DayAvg]
FROM (SELECT average3days,AVG(valueColumn * 1.0) OVER (ORDER BY [date] ROWS BETWEEN CURRENT ROW AND PRECEDING 2) AS [3DayAvg]
      FROM Table
     )t

Just the select Statement within the parethesis is a Problem. I think it is not in order. At least it does not function for me. The following solution works:

select valueColum, AVG(valueColum) over (order by [date] rows between 2  preceding and current row ) as [3DayAvg]
from DAX

Is it for some reason important to multiplie the valueColumn by 1 like visakh suggested it?

The way visakh updates the table (DAX) by this new computed data is not clear to me either. Maybe someone can tell me what kind of update this is or what field within SQL it touches.

I really try hard to understand it!


Thank you very much
Landau

---

update dax2
set [MA3] = d.[ma] from
(select [close], AVG([close]*1.0) over (order by [date] rows between 2  preceding and current row ) as [ma]
from dax2)d

update dax2
set [MA3] = t.[ma] from
(select [date], [close], AVG([close]*1.0) over (order by [date] rows between 2  preceding and current row ) as [ma]
from dax2 ) t
where dax2.[date] = t.[date]

quote:

---

Originally posted by landau66

Hi all!
After reading a lot I found a way to do it:

update dax2
set [MA3] = t.[ma] from
(select [MA3], AVG([close]*1.0) over (order by [date] rows between 2  preceding and current row ) as [ma]
from dax2 ) t
where dax2.[date] = t.[date]

I hope it is a way which is acceptable for an experienced SQL-Programmer!

thank you very much visakh! you gave me valuable hints!!!
Landau

---

update dax2 t
set [MA3] = t.[ma] from
(select [date], [close], AVG([close]*1.0) over (order by [date] rows between 2  preceding and current row ) as [ma]
from dax2 ) t
where dax2.[date] = t.[date]