Compute a running total from Derived Table

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)

Compute a running total from Derived Table

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2009-10-07 : 11:51:49

-- I want to return running totals of costs on each day

-- my query below parses but I get a error when i run the query saying that the table is not updatable

Desired data output BELOW

1/1/2009, 15
1/2/2009, 30
1/3/2009, 35
1/4/2009, 40

-- my query
DECLARE @foo TABLE (
[ID] INT
,AssID int
,[TimeByDay] datetime
, [TaskName] NVARCHAR(255)
, [ParentId] INT
, [cost] MONEY
, rt money -- RT is to hold Running Total
)

INSERT @foo
SELECT 1,null,null,'Design Activities', null, 0,0
UNION SELECT 2,null,null, 'Write Specs', 1, 0,0
UNION SELECT 3,10,'1/1/2009', 'Write UI Spec', 2, 5,0
UNION SELECT 3,10,'1/2/2009', 'Write UI Spec', 2, 5,0
UNION SELECT 3,10,'1/3/2009', 'Write UI Spec', 2, 5,0
UNION SELECT 3,10,'1/4/2009', 'Write UI Spec', 2, 5,0
UNION SELECT 4,11,'1/1/2009', 'Write Data Spec', 2, 5,0
UNION SELECT 4,11,'1/2/2009', 'Write Data Spec', 2, 5,0
UNION SELECT 5,null,null, 'Build Prototypes', 1, 0,0
UNION SELECT 6,12,'1/1/2009', 'Build Screen Mockkups', 5, 5,0
UNION SELECT 6,12,'1/2/2009', 'Build Screen Mockkups', 5, 5,0

declare @RT money
select @RT =0

;WITH CTE
AS
(
SELECT timeByDay, ID, [TaskName],ParentID, Cost,RT
FROM @foo
WHERE ID = 1
UNION ALL
SELECT f.timeByDay, f.ID, f.[TaskName], f.ParentID, f.cost, f.RT
FROM @foo f join CTE c on f.ParentId=c.ID
)

update CTE
Set @RT = RT = @RT + Cost where 1=1

SELECT TimeByDay, sum(RT)
FROM CTE where timeByDay is not null
group by TimeByDay

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-07 : 13:09:53

Why not make the CTE another table variable? That way you can update it.

Jim

Everyday I learn something that somebody else already knew

JohnBGood
Starting Member

48 Posts

Posted - 2009-10-07 : 13:25:18

sounds good but how would i do that?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-07 : 13:33:43

The same way you created the other table variables

DECLARE @cte TABLE(<cols>)
INSERT INTO @cte
SELECT timeByDay, ID, [TaskName],ParentID, Cost,RT
FROM @foo
WHERE ID = 1
UNION ALL
SELECT f.timeByDay, f.ID, f.[TaskName], f.ParentID, f.cost, f.RT
FROM @foo f join CTE c on f.ParentId=c.ID

Jim

Everyday I learn something that somebody else already knew

JohnBGood
Starting Member

48 Posts

Posted - 2009-10-08 : 22:38:41

got it.. thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources