quote:

---

Originally posted by mctaff

thought I'd posted an update here... I adapted the procedural code to my needs and reduced the processing time down to 20 seconds....

thanks for all the assistance!

---

SELECT x.RowDate as Date, avg(y.RowValue)
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY RowDate asc) AS Row, RowDate FROM YourTable) AS x,
(SELECT ROW_NUMBER() OVER (ORDER BY RowDate asc) AS Row, RowDate, RowValue FROM YourTable) AS y
WHERE x.Row between y.Row and y.Row+9
group by x.RowDate
ORDER BY x.RowDate asc

quote:

---

Originally posted by SwePeso

I think set-based code has the fastest time
And no need for staging table either!

-- Prepare sample data
DECLARE	@Sample TABLE (dt DATETIME, Rate FLOAT)

INSERT	@Sample
SELECT	CURRENT_TIMESTAMP - 10,  1 UNION ALL
SELECT	CURRENT_TIMESTAMP -  9,  2 UNION ALL
SELECT	CURRENT_TIMESTAMP -  8,  4 UNION ALL
SELECT	CURRENT_TIMESTAMP -  7,  4 UNION ALL
SELECT	CURRENT_TIMESTAMP -  6,  5 UNION ALL
SELECT	CURRENT_TIMESTAMP -  5,  6 UNION ALL
SELECT	CURRENT_TIMESTAMP -  4,  6 UNION ALL
SELECT	CURRENT_TIMESTAMP -  3,  8 UNION ALL
SELECT	CURRENT_TIMESTAMP -  2,  9 UNION ALL
SELECT	CURRENT_TIMESTAMP -  1, 10 UNION ALL
SELECT	CURRENT_TIMESTAMP -  0, 11 UNION ALL
SELECT	CURRENT_TIMESTAMP +  1,  9

-- Peso 2
SELECT		DATEADD(DAY, dt, '19000101') AS dt,
		AVG(Rate) AS SimpleMovingAvg,
		SUM(wr) AS WeightedMovingAvg
FROM		(
			SELECT	DATEDIFF(DAY, '18991230', dt) AS dt,
				0.1 * Rate AS wr,
				Rate,
				0 AS Actualdate
			FROM	@Sample

			UNION ALL

			SELECT	DATEDIFF(DAY, '18991231', dt),
				0.2 * Rate,
				Rate,
				0
			FROM	@Sample

			UNION ALL

			SELECT	DATEDIFF(DAY, '19000101', dt),
				0.7 * Rate,
				Rate,
				1
			FROM	@Sample
		) AS k
GROUP BY	dt
HAVING		MAX(Actualdate) = 1
ORDER BY	dt

---

E 12°55'05.25"
N 56°04'39.16"

---

quote:

---

Originally posted by jezemine

declare @t table (date datetime not null, rate float not null)

---

elsasoft.org

---