Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Calculating Running Totals
 Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

HershelCohen
Starting Member

USA
1 Posts

Posted - 03/12/2009 :  12:34:57  Show Profile  Reply with Quote
[quote]Originally posted by julianhaynes

I prefer to avoid CROSS joins...

SELECT
a.DayCount
, a.Sales
, SUM(b.Sales) AS RunningTotal
FROM
Sales a
JOIN Sales b ON b.DayCount <= a.DayCount
GROUP BY
a.DayCount
, a.Sales
ORDER BY
a.DayCount
, a.Sales

... but of course this is the same...
Scan count 5000, logical reads 37544, physical reads 0, read-ahead reads 0.

This solution worked for me. Thank you, Julian.
-Hershel
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
652 Posts

Posted - 05/14/2010 :  19:40:37  Show Profile  Reply with Quote
quote:
Originally posted by HershelCohen

[quote]Originally posted by julianhaynes

I prefer to avoid CROSS joins...

SELECT
a.DayCount
, a.Sales
, SUM(b.Sales) AS RunningTotal
FROM
Sales a
JOIN Sales b ON b.DayCount <= a.DayCount
GROUP BY
a.DayCount
, a.Sales
ORDER BY
a.DayCount
, a.Sales

... but of course this is the same...
Scan count 5000, logical reads 37544, physical reads 0, read-ahead reads 0.

This solution worked for me. Thank you, Julian.
-Hershel



I know this is an old post but I just had to warn you... you have no idea what a mistake you made, Hershel... Please see the following article for why I say so...

http://www.sqlservercentral.com/articles/T-SQL/61539/
Go to Top of Page

sageora
Starting Member

USA
4 Posts

Posted - 05/17/2010 :  16:12:24  Show Profile  Reply with Quote
How about using cross apply instead of cross join?
This gives us ability to apply our custom conditions to correlated subquery:

CREATE TABLE [dbo].[Debts](
[id] [int] NULL,
[Value] [float] NULL,
[ag_id] [int] NULL
)

go

insert into dbo.debts(id, ag_id, Value)
values(1, 25, 101.0)
insert into debts(id, ag_id, Value)
values(2, 25, 200.0)
insert into dbo.debts(id, ag_id, Value)
values(3, 35, 1650.0)
insert into dbo.debts(id, ag_id, Value)
values(4, 35, 804.0)
insert into dbo.debts(id, ag_id, Value)
values(5, 35, 525.0)
insert into dbo.debts(id, ag_id, Value)
values(6, 35, 765.0)

go

select o.*, running.*
from dbo.debts o
cross apply(
select SUM(value) sm
from dbo.debts i
where i.id<=o.id --put date field here
and i.ag_id = o.ag_id
) running

Regards

Edited by - sageora on 05/17/2010 16:20:16
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
652 Posts

Posted - 05/23/2010 :  13:46:37  Show Profile  Reply with Quote
quote:
Originally posted by sageora

How about using cross apply instead of cross join?
This gives us ability to apply our custom conditions to correlated subquery:

CREATE TABLE [dbo].[Debts](
[id] [int] NULL,
[Value] [float] NULL,
[ag_id] [int] NULL
)

go

insert into dbo.debts(id, ag_id, Value)
values(1, 25, 101.0)
insert into debts(id, ag_id, Value)
values(2, 25, 200.0)
insert into dbo.debts(id, ag_id, Value)
values(3, 35, 1650.0)
insert into dbo.debts(id, ag_id, Value)
values(4, 35, 804.0)
insert into dbo.debts(id, ag_id, Value)
values(5, 35, 525.0)
insert into dbo.debts(id, ag_id, Value)
values(6, 35, 765.0)

go

select o.*, running.*
from dbo.debts o
cross apply(
select SUM(value) sm
from dbo.debts i
where i.id<=o.id --put date field here
and i.ag_id = o.ag_id
) running

Regards



I haven't tested the particular code above but it looks to me like it has a triangular join in it. See the following article for why that's a bad thing and why it's slower than a cursor and more resource intensive than a cursor.
http://www.sqlservercentral.com/articles/T-SQL/61539/


--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Go to Top of Page

beyt34
Starting Member

1 Posts

Posted - 01/06/2011 :  03:22:54  Show Profile  Reply with Quote
another way using with,
ex:

;WITH TmpTable(DayCount, Sales, RunningTotal)
AS
(
SELECT 0 AS DayCount, CAST(0 AS money) AS Sales, CAST(0 AS money) AS RunningTotal
UNION ALL
SELECT tmp.DayCount,
tmp.Sales,
tmp.Sales + t.RunningTotal AS RunningTotal
FROM #tmpTable AS tmp, TmpTable AS t
WHERE (tmp.DayCount = t.DayCount+1)
)
SELECT *
FROM TmpTable
WHERE DayCount>0
OPTION (MAXRECURSION 32767);
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000