average of next three records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

average of next three records

Author

Topic

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2009-07-29 : 18:06:24

Hi There
is there any way I can take the average of next three records avery time in the table. for example i have a table
CREATE TABLE #MyTable
( ID int NOT NULL,
Number int NOT NULL
)
INSERT #MyTable(ID, Number)
SELECT 1, 1 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 4, 4 UNION ALL
SELECT 5, 6 UNION ALL
SELECT 6, 8 UNION ALL
SELECT 7, 10
I am expecting result
as
ID Avearge
1 2
2 3
3 4 and so on
is any body have any idea how can I do this with out using cursors
Many thanks
Qazafi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-29 : 18:11:31

[code]DECLARE @Sample TABLE
(
ID INT PRIMARY KEY CLUSTERED,
Number INT NOT NULL
)

INSERT @Sample
SELECT 1, 1 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 4, 4 UNION ALL
SELECT 5, 6 UNION ALL
SELECT 6, 8 UNION ALL
SELECT 7, 10

SELECT s.ID,
f.a
FROM @Sample AS s
CROSS APPLY (
SELECT AVG(1.0 * Number)
FROM @Sample AS x
WHERE x.ID BETWEEN s.ID AND s.ID + 2
) AS f(a)[/code]

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

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-30 : 04:12:53

The best use of cross apply I have ever seen :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources