cumlative total by week - 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)

cumlative total by week

Author

Topic

inertia
Starting Member

3 Posts

Posted - 2007-06-13 : 12:44:00

I've found a few statements that almost get what I need, but I'm having a little difficulty tweaking them to meet my needs.

Basic Table:
TransDate DateTime
Amount Decimal

What I'd like to do is get a cumlative total by week.

any help is greatly appreciated...

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-13 : 13:00:16

Can you post some sample data and the expected out put so we can understand the question better?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

inertia
Starting Member

3 Posts

Posted - 2007-06-13 : 15:26:41

Sample data would like this:
datetime Amount
6/4/2007 01:12:00 100.00
6/4/2007 02:33:00 50.00
6/5/2007 13:40:00 80.00
6/11/2007 01:12:00 100.00
6/12/2007 13:10:0 100.00

results should look something like:
Week CumlativeAmount
23 230.00
24 430.00

(I suppose if I were to get fancy I'd also add a weekly sum column)
thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-13 : 15:48:35

select datepart(week,yourdate) as [Week], sum(Amount) as Total
from yourtable
group by datepart(week,yourdate)

- Jeff
http://weblogs.sqlteam.com/JeffS

inertia
Starting Member

3 Posts

Posted - 2007-06-13 : 15:57:20

Jeff,
close, but that provides a sum for each week.
I'm looking for a cumlative total by week (see above expected results)
thanks for the help...

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-14 : 00:11:35

Create table tt(dt datetime, Amount decimal)
insert tt
select '6/4/2007 01:12:00', 100.00 union all
select '6/4/2007 02:33:00', 50.00 union all
select '6/5/2007 13:40:00', 80.00 union all
select '6/11/2007 01:12:00', 100.00 union all
select '6/12/2007 13:10:0', 100.00 union all
select '7/12/2007 13:10:0', 100.00 union all
select '7/13/2007 13:10:0', 100.00
Go
Create function fn_cumsum(@wk int)
returns decimal
as
begin
declare @sum decimal
Select @sum = Coalesce(@sum, 0) + sum(amount)
from tt where datepart(ww, dt) <= @wk
Return @sum
End
Select datepart(ww, dt) as [Week],
Sum(amount) as [Sum],
dbo.fn_cumsum(datepart(ww, max(dt))) as [Cumulative Sum]
from tt
group by datepart(ww, dt)

--------------------------------------------------
S.Ahamed

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-14 : 09:15:52

Where do you want to show data?
If you use Reporting tool make use of its Running Total feature

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources