Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5 Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, February 13, 2008 11:30 PM
 Valued Member Group: General Forum Members Last Login: Monday, April 20, 2015 9:20 AM Points: 74, Visits: 454
 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 companies I've had the fortune/misfortune to work with since the year 2000, have all used the 2k version... finally found 1 that actually did use 2k5 and had to give them up because they were slaughtering the database, the data, and the server.I can't comment on that. ;) I'm sure someone will see this post and who I work for and fire me if I did. Christopher Ford
Post #455529
 Posted Thursday, February 14, 2008 1:23 AM
 Ten Centuries Group: General Forum Members Last Login: Sunday, November 13, 2016 6:03 PM Points: 1,081, Visits: 1,013
 Christopher Ford (2/13/2008)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 AmountThisDate, SUM(J2.Amount) AS RunningBalance, CAST(AVG(1.*J2.Amount) AS DECIMAL(12, 2)) AS AvgRunningBalanceFROM dbo.JBMTest AS J1 JOIN dbo.JBMTest AS J2 ON J2.AccountID = J1.AccountID AND (J2.Date >= '01/01/2000' --Anchor Date from example AND J2.Date <= J1.Date)GROUP BY J1.AccountID, J1.Date, J1.AmountORDER BY J1.AccountID, J1.Date;`Not quite as elegant as SUM(SAL) OVER()But I'm willing to bet that the query plans generated for both would be similiar to this query.Credit doesn't go to me for this method. Learned this, like many other things from other people. This method gets credited to Itzik, since I learned it from one of his many articles, but it's useful in so many circumstances.And this one works on SQL 2000. =)Sadly that set based solution in my system over several million rows has a duration of about a half hour. The update variable nips through it in 10 seconds.
Post #455552
 Posted Thursday, February 14, 2008 7:40 AM
 Valued Member Group: General Forum Members Last Login: Monday, April 20, 2015 9:20 AM Points: 74, Visits: 454
 Sadly that set based solution in my system over several million rows has a duration of about a half hour. The update variable nips through it in 10 seconds.Yeah, I can understand that, after I ran it, I ran it with Statistics IO on.because of the way the JOIN works, it basically rescans the table quite a few times on the right side of the JOIN, J2 gets scanned like 10000 times.BUT, it works on SQL 2000. :) Christopher Ford
Post #455698
 Posted Thursday, February 14, 2008 8:06 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, January 6, 2016 2:10 PM Points: 1,323, Visits: 1,107
 Christopher Ford (2/13/2008)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 AmountThisDate, SUM(J2.Amount) AS RunningBalance, CAST(AVG(1.*J2.Amount) AS DECIMAL(12, 2)) AS AvgRunningBalanceFROM dbo.JBMTest AS J1 JOIN dbo.JBMTest AS J2 ON J2.AccountID = J1.AccountID AND (J2.Date >= '01/01/2000' --Anchor Date from example AND J2.Date <= J1.Date)GROUP BY J1.AccountID, J1.Date, J1.AmountORDER BY J1.AccountID, J1.Date;`Not quite as elegant as SUM(SAL) OVER()This is precisely the Triangular Join mentioned in the first paragraph of the article. This is what Jeff is trying to get rid of.--JimFive
Post #455717
 Posted Thursday, February 14, 2008 8:23 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 6:26 AM Points: 7,504, Visits: 17,941
 Christopher - First off - nice CTE solution. However - the order by inside of the ROW_NUMBER() only guarantees the order in which the numbers are assigned, and not the physical order in which things are returned. So - unless you use the same parlor room tricks Jeff is advocating, you're likely to run into the same issues everyone else is talking about. Without guaranteeing the order, you have no guarantees that the "previous row" is the row you should be pulling the running total from.I'll refer you to this: http://blogs.msdn.com/queryoptteam/archive/2006/05/02/588731.aspxSo again - without the clustered index in there to help you, this solution STILL can fall apart....If you want to see - drop the clustered index, or change it to a non-clustered index.Of course - if you DO take into account those caveats previously mentioned (clustered index, WITH(INDex(), TabLock) OPTION (MAXDOP 1), no partitioned tables), it's very elegant. ----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #455733
 Posted Thursday, February 14, 2008 8:41 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 11:13 AM Points: 5,645, Visits: 8,171
 VERY nice find on that blog post! It sums up the ordered-output issue(s) quite explicitly. Best,Kevin G. BolesSQL Server ConsultantSQL MVP 2007-2012TheSQLGuru at GMail
Post #455753
 Posted Thursday, February 14, 2008 8:53 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 6:26 AM Points: 7,504, Visits: 17,941
 Conor had a lot of interesting stuff in the few blog posts he has made - like rules of thumb when queries will recompile, why Top 100 PERCENT is evil, etc... I'm disappointed that he seems to have given up on it (nothing new in a few years). ----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #455769
 Posted Thursday, February 14, 2008 11:07 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, December 1, 2015 6:09 AM Points: 2,891, Visits: 3,889
Post #455879
 Posted Thursday, February 14, 2008 11:35 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 6:26 AM Points: 7,504, Visits: 17,941
 Cool - something to add to my reading list....I'll have to send him a stern talking to about orphaning blog readers like that! (I'm so sure he'll care about that.....:P)Thanks! ----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #455895
 Posted Thursday, February 14, 2008 2:19 PM
 Valued Member Group: General Forum Members Last Login: Monday, April 20, 2015 9:20 AM Points: 74, Visits: 454
 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 2005 and one for SQL 2000. I'm quite sure there's a much simpler solution that also guarantee's the row output. These weren't quite as speedy...in fact, the sql 2000 one really chewed the I/O a bit. Can't imagine why. =)This is what happens when you think too hard as well. =) Or get nit picky on something like order... bah...who cares about order?? :D It's too hard to write queries that require order and updates and in order and accumulate. ;)I will say that I haven't had quite as much fun coding as I have trying to come up with alternative ways to do Running Totals since finding this article.`SET STATISTICS IO ON;---- Guarantee Order Returned ----WITH RunningTotal AS( SELECT AccountID, Date, Amount, GrpBal, ROW_NUMBER() OVER(PARTITION BY AccountID ORDER BY AccountID, Date) AS rn FROM dbo.JBMTest)SELECT Cur.AccountID, Cur.Date AS Date, Cur.Amount AS Amount, ISNULL(Cur.Amount + Prv.Amount,cur.Amount) AS grpBalFROM RunningTotal AS Cur LEFT OUTER JOIN RunningTotal AS Prv ON Cur.AccountID = Prv.AccountID AND Cur.rn - 1 = Prv.rnORDER BY Cur.AccountID, Cur.Date;---- Really thinking too hard and order guarantee -----SELECT a.AccountID, a.RowNum, a.Date AS Date, a.Amount AS Amount, ISNULL(a.Amount + b.Amount,a.Amount) AS grpBalFROM (SELECT AccountID, RowNum, Date, Amount, (SELECT TOP(1) RowNum FROM dbo.JBMTest AS A2 WHERE A2.AccountID = A1.AccountID AND (A2.Date < A1.Date OR A2.Date = A1.Date AND A2.RowNum < A1.RowNum) ORDER BY Date DESC, RowNum DESC) AS PrvRowNum FROM dbo.JBMTest AS A1) AS a LEFT OUTER JOIN dbo.JBMTest AS b ON a.PrvRowNum = b.RowNumORDER BY a.AccountID, a.date, a.rowNum;` Christopher Ford
Post #456001

 Permissions