Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)


Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45189 Visits: 39925
Comments posted to this topic are about the item Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)


Comments posted to this topic are about the item Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)


*** NOTICE *** This article is in the process of (hopefully) the final "rewrite". *** NOTICE ***

In the meantime, you'll be very interested to know that Paul White and Tom Thompson have a method of error checking that not only checks for errors virtually without any additional overhead, the method they came up with also helps further guarantee the correct sort order for the updates. You can find an example of the coded method at the following URL which will lead to you a post very late in this thread...
http://www.sqlservercentral.com/Forums/FindPost981258.aspx

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Allister Reid
Allister Reid
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 3073
Hi Jeff,
many thanks for taking the time to research, write and post this eagerly anticipated article!

Just tried running some of the code and have noticed missing whitespace from figures 2, 3, 6, 6, 18, 19 (maybe others, haven't got all the way through yet) resulting in syntax errors.

You seem to be losing your ongoing battle with these code windows!


Again thanks a million for another great article.


Allister
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45189 Visits: 39925
Thanks Allister. The reformatting is taking longer than any of us anticipated and I also have a late breaking bit of information that I'm adding.

For the code windows, you'll need to copy from one line above the code window to one line below the code window, paste to Word (or other word processor), copy from that and paste to QA or SSMS. As soon as we get the formatting and the additional info squared away, we'll add a file with clearly marked code for all the examples to make it just a little easier for folks to test with.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
peter-757102
peter-757102
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 2549
One word Jeff....CLASS

Thank you for the very clear article and all the testing done to make sure that what you claim is 100% correct. But I wouldn't be me if I had not at least some comments, so here they come:

1) In Figure 28 I personally found the switch from the 3 part assignment problem, solved by using 2 part assignment, straight to a "this is the correct way" which shows 3 parts again...confusing. In fact I think I still don't get the reason why one works and the other doesn't (and I would go 2 parts all the way because of this).

2) At the end when talking about a trigger method to keep a running total correct at all times, you state that it might not work well with batch updates. It is hard to argue with a 'may not work well' opinion, but I just want to state that I can think of a way that could in fact work well.

What about making some sort of temporary Delta/Offset table first for those records that changed and then use this as input in a batch update that corrects all depending records with the appropriate offset without using a pseudo cursor.

Its just an idea, I never tested it and cannot guarantee it runs into unforeseen problems either.
ben.mcintyre
ben.mcintyre
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 403
Superlative. As usual, you make a highly complex and conditional task look simple and straightforward by precisely covering key points. I am now not afraid to try using this method :-)
Any time frame on your part 2 of pivot tables article Jeff ? I was thinking of drafting up a complementary article but wanted to leave you to have a complete say first ...
TravisDBA
TravisDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1472 Visits: 3069
Good article Jeff, and I did read Pauls blog on DBCC PAGE AND DBCC IND and Paul says the following about those undocumented commands:

"Before jumping into how things work, I'd like to go over two commands I'll be using a lot - DBCC PAGE and DBCC IND. These are both undocumented and unsupported commands, but are very safe to use as they're used extensively inside and outside Microsoft when troubleshooting. Nevertheless, use at your own risk. They're quite well known in the SQL community and I and others have publicized them before."

I have followed the genius of Paul S Randal for quite sometime and have rarely ever seen him to be off-base about anything in SQL Server. So, if the author of DBCC CHECKDB tells me that they are "very safe", then I pretty much take that to the bank. When it comes to SQL Server internals there is no one I trust more than Paul, maybe Kim Tripp. It's a toss up. Now, on the otherhand, he does say to use them at your own risk, but in reality you could say that about all undocumented commands in SQL Server. Microsoft tends to cover themselves there and I don't blame them. However, that does not necessarily mean that they are not useful or safe. Smile They are probably not commands for the "database cowboys" as I like to say sometimes, but in the hands of someone who relatively knows what they are doing, they are indispensible...Travis.

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
DM Unseen
DM Unseen
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 286
I'm missig the CTE/Recursive approaches to solving these issues. While usually not single pass the beat triangular joins

For Running total see: http://shahamishm.blogspot.com/2009/04/how-to-do-running-sum-using-cte-sql.html

For finding previous row ordered by key_nr and trans_date(clustered key):

WITH trans_cte(key_nr, trans_date,field1,field2,row_order)
AS(
SELECT
key_nr
,trans_date
,field1
,field2
,ROW_NUMBER()OVER(PARTITIONBY key_nr ORDERBY trans_date ASC) row_order
FROM [dbo].[trans_stage_table]
)
SELECT BASE.key_nr
,BASE.trans_date
,BASE.field1
,BASE.field2
FROM trans_cte BASE
LEFTJOIN trans_cte LAG ON BASE.key_nr = LAG.key_nr
AND BASE.row_order = LAG.row_order+1
and BASE.field1 = LAG.field1
and BASE.field2 = LAG.field2
where LAG.key_nr isnull

Kind Regards,

DM Unseen AKA M. Evers
ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 494
That's an awesome article Jeff!

Now if only I could work out how to run a query that traverses a directed graph with cycles - but where there is a node that connects to another node that had already been traversed then it does not continue. ..

But rather than muck up this thread, I'll post a question about it a bit later. :-)

Random Technical Stuff
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45189 Visits: 39925
peter-757102 (11/10/2009)
One word Jeff....CLASS

Thank you for the very clear article and all the testing done to make sure that what you claim is 100% correct. But I wouldn't be me if I had not at least some comments, so here they come:

1) In Figure 28 I personally found the switch from the 3 part assignment problem, solved by using 2 part assignment, straight to a "this is the correct way" which shows 3 parts again...confusing. In fact I think I still don't get the reason why one works and the other doesn't (and I would go 2 parts all the way because of this).

2) At the end when talking about a trigger method to keep a running total correct at all times, you state that it might not work well with batch updates. It is hard to argue with a 'may not work well' opinion, but I just want to state that I can think of a way that could in fact work well.

What about making some sort of temporary Delta/Offset table first for those records that changed and then use this as input in a batch update that corrects all depending records with the appropriate offset without using a pseudo cursor.

Its just an idea, I never tested it and cannot guarantee it runs into unforeseen problems either.


Thanks, Peter. I appreciate the awesome compliment and your observations.

Referring to your item 1, I believe that part of the reason why the code in the "OMG! I Broke It!" section is a bit confusing is because (going out on a limb here), 99.999% of the people wouldn't add 2 to 1 to get to 3. When I first saw a similar bit of code from the two guys I mentioned in the article, they only used the number 1... talk about confusing. Like a lot of folks might, I initially concentrated on the "Who would do this?" rather than the "Oh... I don't know why it does that, but it does." :-P

What the intent of that code is is to show that the 3 part update can create a problem (not always) if the expression (right most part of the 3 part update) relies on the same variable that has already been updated by another expression. The other intent is to show that any problems with the 3 part update can be easily overcome by splitting the 3 part update into two 2 part updates. I see in another post above where at least one person has decided to keep life simple and use only 2 part updates all the time.

For your item number 2, you are correct that you could easily copy the changed rows to a Temp table and do a batch update on those... however, just offseting the rows (I assume you're talking about using an incremental column somewhere in that) won't keep you from needing a "Quirky Update", a real cursor, or a Triangular Join to update the new rows with a running balance. The point is well taken, though. The article is basically incomplete unless the method of using a trigger to update on input is included. I need to resubmit the article to spell Paul Randal's name correctly and to put the correct code in Figure 21... I'll add a section for the trigger update.

Again, thank you for the very thoughtful observations. They're much appreciated.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
Hi Jeff,

Thanks for doing a rewrite of this article.

Just a few comments before I start really reading the article:

1) Being one of the people who tried to grill you over a small fire for your recommendation to use what you call quirky update, I feel olbliged to point out that, no matter how many tests you run succesfully and how many rules you add to rule out problem cases, using undocumented features will always remain a huge risk. Of course, you are right that you can't prove reliability of SELECT either. But if I ever encounter some weird case where SELECT does not behave as expected and as documented, I can file a bug with Microsoft and have it fixed. If I encounter a case where the quirky update fails to work, I can try to file a bug but I should expect the MS guys to do the told-you-so-dance.

2) The code in figure 2 will blow up on any server with a case sensitive collation. You should change "TempDB" to "tempdb".

That's it for now. I'll now start properly reading the article.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search