quote:

---

The reason for the performance difference is that the Celko solution and it's variants (all the v1 < v2 ones) are quadratic. To compute the total for the Nth rcord, it computes the total of the N-1 records below. When it moves to computing the next total, it redoes all this work and add the Nth value. On average, each total requires N/2 records to be accessed. Do it for a dataset twice the size and you should see ti take about 4 times as long. Not acceptable where thevolume of data can increase significantly over time.

The cursor appraoch visits each record just once.

The update approach is interesting. The only problem is ensuring that the records come in the right order. I am considering copying the data in a temp table with a clustered index on the ordered field to force the records into the required order but the docs do not guarantee the update will take the records in clustered index order. And you can't have an ORDER BY in an update.

quote:

---

Interesting <P>Hmmmm

Very interesting about the cursor being quicker. Just proves that there never is one hard and fast rule for anything.

---

---

quote:

---

You received the following message from mschweisguth:

I was looking at your SQL. I once did something like this, but, I was afraid that it was undocumented behavior.

Primarily since I remembered reading something, somewhere, that said that even if you add a clustered index to a table, a select * statement isn't gaurenteed to return ordered output unless you use an order by clause.

Do you know if this is specified someplace in SQL92?

-thanks.

---

quote:

---

Originally posted by JustinBigelow

Why the need for coalesce? <P>When I started to read the article I decided to try my own solution first and compare it to what the author came up with. My solution is as follows:

select s1.daycount, s1.sales,
RunningTotal = (select sum(s2.sales) from sales s2
where s2.daycount <= s1.daycount)
from sales s1
orderby s1.daycount, s1.sales

I came up with this after refreshing myself on correlated sub queries in "Professional SQL Server 7.0 Programming" so I guess you could call this the "Wrox Solution" ;)

This is pretty close to the "Celko" solution so I timed both on QA and the Wrox beat the Celko by a whopping ONE SECOND (heavy sarcasm)! I don't understand the need for the coalesce function, they both seem to produce the same results. Would somebody mind enlightening me? Thanks.

Justin

---

quote:

---

Originally posted by mono

The cursor appraoch visits each record just once.



The update approach is interesting. The only problem is ensuring that the records come in the right order. I am considering copying the data in a temp table with a clustered index on the ordered field to force the records into the required order but the docs do not guarantee the update will take the records in clustered index order. And you can't have an ORDER BY in an update.

---

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

---

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

---