SQL Running Total - 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

General SQL Server Forums

New to SQL Server Programming

SQL Running Total

Author

Topic

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-07-01 : 03:37:01

Hi Guys.

I can't get my head around this.

I have been task to give a running total on the following table

Budget
ProjectCode BudgetMonth BudgetYear ExpenseBudget
mvw001 07 2009 1000.00
mvw001 08 2009 2000.00
mvw001 09 2009 1000.00

Can anyone help me out here?

This is what i have so far...
select
a1.ProjectCode,
a1.BudgetMonth,
a1.BudgetYear,
a1.ExpenseBudget,
SUM(a2.ExpenseBudget) Running_Total_EB
from
Budget a1,
Budget a2
where
a1.ExpenseBudget <= a2.ExpenseBudget
or
(
a1.ExpenseBudget = a2.ExpenseBudget
and
a1.ProjectCode = a2.ProjectCode
and
a1.BudgetMonth = a2.BudgetMonth
and
a1.BudgetYear = a2.BudgetYear
)
GROUP BY
a1.BudgetYear,
a1.BudgetMonth,
a1.ProjectCode,
a1.ExpenseBudget

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 03:38:36

try searching for running total in this forum. Lots of example here

KH
[spoiler]Time is always against us[/spoiler]

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-07-01 : 03:51:07

Thanks.

I have searched the forum and this is where i am.

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-07-01 : 04:02:50

I have even tired it this way.
select
ProjectCode,
BudgetMonth,
BudgetYear,
ExpenseBudget,
ExpenseBudget+COALESCE(

(
select SUM(ExpenseBudget)
from Budget b

where

b.BudgetMonth <= a.BudgetMonth
),0) AS RunningTotal

from Budget a

But yet the figures do not match up.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 04:23:31

are you using SQL 2000, 2005 or 2008 ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 04:27:40

quote:
Originally posted by mvanwyk

Thanks.

I have searched the forum and this is where i am.

seen this ?
http://www.sqlteam.com/article/calculating-running-totals

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 12:18:31

SQL 2000:-


SELECT b.ProjectCode,	
b.BudgetMonth,	
b.BudgetYear,	
b.ExpenseBudget,
(SELECT SUM(ExpenseBudget) FROM Budget WHERE ProjectCode=b.ProjectCode		AND 100*BudgetYear+BudgetMonth <= 100*b.BudgetYear+b.BudgetMonth) AS ExpenseBudget
FROM Budget b

if sql 2005:-


SELECT b.ProjectCode,	
b.BudgetMonth,	
b.BudgetYear,	
b.ExpenseBudget,
c.ExpenseBudget
FROM Budget b
CROSS APPLY (SELECT SUM(ExpenseBudget) AS ExpenseBudget 
             FROM Budget 
             WHERE ProjectCode=b.ProjectCode	
             AND 100*BudgetYear+BudgetMonth <= 100*b.BudgetYear+b.BudgetMonth)c

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources