Login    Forum    Search    FAQ   Blog

Board index » Server Side Development » SQL




 Page 1 of 1 [ 5 posts ] 



Author Message
 Post subject: Compute a runnin tot just frm a Derived Table
 Post Posted: Thu Nov 19, 2009 6:56 am 
Offline

Joined: Sat Feb 21, 2009 1:05 pm
Posts: 66
I need to return runnin totals of the costs on each n every day

-- my query below parses however I m gettin an error when i have run the
query dat says da tab was updatable

Code:
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

_________________
If your wife wants to learn to drive, don't stand in her way.


Top 
 Post subject: Re: Compute a runnin tot just frm a Derived Table
 Post Posted: Thu Nov 19, 2009 6:57 am 
Offline

Joined: Sat Feb 21, 2009 11:06 am
Posts: 1794
Why not make da CTE anothr tabl variable? That way you could update it.


Top 
 Post subject: Re: Compute a runnin tot just frm a Derived Table
 Post Posted: Thu Nov 19, 2009 6:57 am 
Offline

Joined: Sat Feb 21, 2009 1:05 pm
Posts: 66
sounds perfect dat how would i do that?

_________________
If your wife wants to learn to drive, don't stand in her way.


Top 
 Post subject: Re: Compute a runnin tot just frm a Derived Table
 Post Posted: Thu Nov 19, 2009 6:57 am 
Offline

Joined: Sat Feb 21, 2009 11:06 am
Posts: 1794
The same way u created da othr tab vars

Code:
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


Top 
 Post subject: Re: Compute a runnin tot just frm a Derived Table
 Post Posted: Thu Nov 19, 2009 6:57 am 
Offline

Joined: Sat Feb 21, 2009 1:05 pm
Posts: 66
got it.. thanks!

_________________
If your wife wants to learn to drive, don't stand in her way.


Top 
Display posts from previous:  Sort by  
 
 Page 1 of 1 [ 5 posts ] 




Board index » Server Side Development » SQL


Who is online

Users browsing this forum: No registered users and 1 guest

 
 

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron