quote:

---

Originally posted by Peso

Further optimized for SQL Server 2005 (only a third of the reads for SQL Server 2000 approach).
Jezemine, if you have SQL Server 2005, can you timetest this approach for SQL Server 2005 too?

---

method     time (ms)
---------  ---------
cursor         14630
Peso 2005       3890
Peso 2000       3610

quote:

---

Originally posted by Peso

Further optimized for SQL Server 2005 (only a third of the reads for SQL Server 2000 approach).
Jezemine, if you have SQL Server 2005, can you timetest this approach for SQL Server 2005 too?

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

-- SQL Server 2005
SELECT		DATEADD(DAY, p.ActualDate, '19000101') AS dt,
		AVG(p.Rate) AS SimpleMovingAvg,
		SUM(
			CASE p.dt
				WHEN 'dt0' THEN p.r0
				WHEN 'dt1' THEN p.r1
				WHEN 'dt2' THEN p.r2
				ELSE 0
			END
		) AS WeightedMovingAvg
FROM		(
			SELECT	Rate,
				DATEDIFF(DAY, '19000101', dt) AS dt0,
				0.7 * Rate AS r0,
				DATEDIFF(DAY, '18991231', dt) AS dt1,
				0.2 * Rate AS r1,
				DATEDIFF(DAY, '18991230', dt) AS dt2,
				0.1 * Rate AS r2
			FROM	@Sample
		) AS y
UNPIVOT		(
				ActualDate
			FOR	dt IN (y.dt0, y.dt1, y.dt2)
		) AS p
GROUP BY	p.ActualDate
HAVING		MIN(p.dt) = 'dt0'
ORDER BY	p.ActualDate

-- SQL Server 2000
SELECT		DATEADD(DAY, k.dt, '19000101') AS dt,
		AVG(k.Rate) AS SimpleMovingAvg,
		SUM(k.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	k.dt
HAVING		MAX(k.Actualdate) = 1
ORDER BY	k.dt

---

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

---

SELECT		DATEADD(DAY, v.Number, s.dt),
		AVG(1.0E0 * s.Rate)
FROM		@Sample AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number < @Days
GROUP BY	DATEADD(DAY, v.Number, s.dt)
HAVING		COUNT(*) = @Days
ORDER BY	DATEADD(DAY, v.Number, s.dt) DESC

quote:

---

Originally posted by Peso

SELECT		DATEADD(DAY, v.Number, s.dt),
		AVG(1.0E0 * s.Rate)
FROM		@Sample AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number < @Days
GROUP BY	DATEADD(DAY, v.Number, s.dt)
HAVING		COUNT(*) = @Days
ORDER BY	DATEADD(DAY, v.Number, s.dt) DESC

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by Peso

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"

---

SELECT		y.RowNumber AS rn, y.[close] as cl,
		AVG(y.[close]) AS SimpleMovingAvg
FROM		(
			SELECT	RowNumber, FTS.dbo.test.[close] FROM	FTS.dbo.test where RowNumber between 1 and 50
 
	
		) AS y
GROUP BY	y.RowNumber, y.[close]
ORDER BY	y.RowNumber

DECLARE	@Sample TABLE
	(
		RowNumber INT,
		RowDate DATETIME,
		RowValue DECIMAL(10, 2)
	)

INSERT	@Sample
	(
		RowNumber,
		RowDate,
		RowValue
	)
SELECT	1, '2005-01-02 22:00', 196.96 UNION ALL
SELECT	2, '2005-01-02 22:15', 196.75 UNION ALL
SELECT	3, '2005-01-02 22:30', 196.98 UNION ALL
SELECT	4, '2005-01-02 22:45', 196.90 UNION ALL
SELECT	5, '2005-01-02 23:00', 196.85

-- Calculate moving average
DECLARE	@Items INT

SET	@Items = 3

SELECT		DATEADD(MINUTE, 15 * v.Number, s.RowDate),
		AVG(1.0E0 * s.RowValue)
FROM		@Sample AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number < @Items
GROUP BY	DATEADD(MINUTE, 15 * v.Number, s.RowDate)
HAVING		COUNT(*) = @Items
ORDER BY	DATEADD(MINUTE, 15 * v.Number, s.RowDate)

quote:

---

Originally posted by mctaff

RowNumber, Date, Close
1, 2005-01-02 22:00:00.000, 196.96
2, 2005-01-02 22:15:00.000, 196.75
3, 2005-01-02 22:30:00.000, 196.98
4, 2005-01-02 22:45:00.000, 196.90
5, 2005-01-02 23:00:00.000, 196.85
etc...

sorry I'm not sure how to format the data to display very well...

for each row, I'd then like to see the moving average of the previous 'x' rows - eg for row 6, the average of rows 1 to 5, for row 7 the average of rows 2 to 6 etc... I would like the 'x' value to be a parameter that is easy to change


Thanks for the assistance...

David.

---