For totals that involve groups, I wonder if you can split the work in half.

A multi-thread CLR?

Where the first query get the first half while the second query gets the...

You know...with very little effort, Frank's query could easily be adapted for a self referencing table to do a rolling update in a guaranteed order for each child under a...

Sir Slicendice (2/15/2008)

---

OK, some fun on a slow Friday afternoon: this can indeed be implemented so that it is very, very quick without breaking any of the rules/guarantees provided by...

Matt Miller (2/15/2008)

---

Christopher Ford (2/15/2008)

---

I'm going to guess that implementing the logic of sum(detail)=Total Payment would best be implemented on the application side in a .Net class library?

I can't think...

Matt Miller (2/15/2008)

---

I know of two basic strategies. The difference between the two is whether you apply a specific payment to a specific bill, or if it just gets...

Matt,

Perfect, thank you. That helps a lot.

I was worried about the dynamic calculation of it all and wasn't sure if that was the way to go for figuring out...

I looked at this post on the forum:

http://www.sqlservercentral.com/Forums/Topic403433-361-1.aspx

But that didn't really help explain anything. =)

He already had a "plan" for handling payments.

I do not really have a plan...

TheSQLGuru (2/15/2008)

---

>>Well...There's got to be a way to use the CTE and ROW_NUMBER() and guarantee an ordered update...

Sorry, but there doesn't have to be. Just because we really wish...

ALZDBA (2/15/2008)

---

Well it certainly started with a great article, handling a fairly common issue 😎

And it resulted in a solution that worked a lightning speed and performed

the actual update in...

Jeff Moden (2/14/2008)

---

Yep... tested the heck out of the CTE solution with ROW_NUMBER() for the running balance... without the clustered index on the appropriate columns, if falls apart in that...

Fine...

So...solutions which guaratee order in which rows are processed is it? Man, you guys are hard to please.

Here's 2 solutions I can think of. One for SQL...

Yeah,...

Jeff Moden (2/13/2008)

---

Well stated in both posts, Christopher... But, you've been spoiled by SS 2k5... none of that works in 2k and, I don't know about other folks, but the...

Earlier Jeff posted a question on using SUM(SAL) OVER()

Since OVER() doesn't support ORDER BY when used with an aggregation.

How about this one?

SELECT J1.AccountID,

J1.Date,

J1.Amount AS...

The previous solution also works with parallel IO on 64 bit boxes quite well, because you're updating the results of your CTE which get pushed back to the underlying table.

Things...