A Set-Based Solution - 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 2008 Forums

Transact-SQL (2008)

A Set-Based Solution

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-17 : 12:36:39

Hi,
Who can post a solution for solving the problem without using Cursor/loop?
Is there any chance for solving this with quirky update or recursive CTE?


Declare @t Table
(
	Id identity primary key,
	Value int
)

Insert @t 
Values (10), (20), (50), (10)

My desired result:

Id------------value------------new_value
1              10                 10               (10) / 1 
2              20                 15               (10 + 20) / 2
3              50                 25               (10 + 15 + 50) / 3
4              10                 15               (10 + 15 + 25 + 10) / 4

______________________

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-17 : 19:33:38

[code]-- Previous sample data
DECLARE @t TABLE
(
ID INT IDENTITY(1, 1) PRIMARY KEY,
Value INT
)

INSERT @t
VALUES (10),
(20),
(50),
(10)

-- Solution starts here
;WITH cteSource
AS (
SELECT ID,
Value,
Value AS Aggregated,
Value AS Accumulated
FROM @t
WHERE ID = 1

UNION ALL

SELECT t.ID,
t.Value,
(s.Accumulated + t.Value) / t.ID AS Aggregated,
s.Accumulated + (s.Accumulated + t.Value) / t.ID AS Accumulated
FROM cteSource AS s
INNER JOIN @t AS t ON t.ID = s.ID + 1
)
SELECT ID,
Value,
Aggregated
FROM cteSource
ORDER BY ID[/code]There is a formula you can use too but the involved cross apply is costly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources