Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten) Expand / Collapse
Author
Message
Posted Wednesday, October 14, 2009 2:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #802558
Posted Saturday, October 17, 2009 8:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 09, 2014 9:24 AM
Points: 192, Visits: 3,073
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
Post #804688
Posted Saturday, October 17, 2009 9:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #804700
Posted Tuesday, November 10, 2009 3:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:35 AM
Points: 319, Visits: 2,151
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.
Post #816385
Posted Tuesday, November 10, 2009 4:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:44 AM
Points: 76, Visits: 379
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 ...
Post #816400
Posted Tuesday, November 10, 2009 4:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 1:05 PM
Points: 1,334, Visits: 3,068
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. :) 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. ..."
Post #816404
Posted Tuesday, November 10, 2009 5:00 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 17, 2013 1:28 AM
Points: 8, Visits: 284
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
Post #816407
Posted Tuesday, November 10, 2009 5:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 03, 2011 7:09 AM
Points: 258, 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
Post #816414
Posted Tuesday, November 10, 2009 6:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
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."

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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #816440
Posted Tuesday, November 10, 2009 6:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 5,794, Visits: 8,006
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
Post #816441
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse