SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculate the Running Total for the last five Transactions


Calculate the Running Total for the last five Transactions

Author
Message
mike brockington
mike brockington
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 245
Is no one else going to point out that the results of this process are flawed: the first four values are given as zero, which is just plain wrong! (One could argue that in some cases they should return NULL, but never zero.)

Throw away your pocket calculators; visit www.calcResult.com


Anipaul
Anipaul
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12629 Visits: 1407
Very good point pointed out by Mike. It will be never zero.



Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117288 Visits: 9672
I've had weirder requests than this... sometimes the user wants the data if and only if a certain amount of days has been taken into account.

But I agree... that looks funny to me too!
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)

Group: General Forum Members
Points: 168763 Visits: 39530
I'll address a couple of concerns from my point of view.

1. Based on the article, the first 4 values should be zero. This is why I put an isnull in my code. If you take that out, the first four values will be null.

2. The identity column. Again, based on the article, the sole purpose of the identity column is to provide an order to the values inserted into the table. By making this column a primary key, I also made this a clustered index allowing it to force the order specified. By Jeff's way of doing this, I also should have put an index hint on the update query as well, but for this demonstration it worked well without it. Since the purpose of the identity field was order, if there was a gap it wouldn't have caused an issue.

Some of the other things that have been brought up are actually extensions of this article, and I challenge those individuals to pursue those and show us what you find, either as a post here, or write another article expanding on what the author started.

This is how we learn new tricks and ways to accomplish a variety of tasks.

I reran my queries, once with the initial data set, and a second time with a larger data set. Below are the row counts, statistics io, ans statistics time from the runs.


row count: 20
-- Cross Join Query --
Table 'Accounts'. Scan count 17, logical reads 74, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
-- Cross Join Query --
-- Update Query --
Table 'Accounts'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 44 ms.
-- Update Join Query --
-- Select After Update Query --
Table 'Accounts'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
-- Select After Update Query --


Row Count: 3280
-- Cross Join Query --
Table 'Accounts'. Scan count 3278, logical reads 6663, physical reads 0, read-ahead reads 49, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 246 ms.
-- Cross Join Query --
-- Update Query --
Table 'Accounts'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 49, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 35 ms.
-- Update Join Query --
-- Select After Update Query --
Table 'Accounts'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 38, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 201 ms.
-- Select After Update Query --


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)

Group: General Forum Members
Points: 382388 Visits: 43014
Hugo Kornelis (11/27/2008)
What I was refering too is the lack of ROW_NUMBER() in SQL Server 2000. This means you'll either have to take your chance with IDENTITY, at the risk of gaps, as the author of this article did; or you have to use a correlated subquery to calculate the row number on the fly, which can result in dramatic performance as the amount of rows grows. Plus, the queries tend to get long and hard to understand.


Nope... in SQL Server 2000, just do a SELECT INTO a temp table with the IDENTITY function and the author's code works just fine without any difficulty for length or understanding.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)

Group: General Forum Members
Points: 382388 Visits: 43014
Lynn Pettis (11/26/2008)
Good article, but let's take another approach and see what we see.


Heh... you beat me to it, again. Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)

Group: General Forum Members
Points: 168763 Visits: 39530
Jeff Moden (11/27/2008)
Lynn Pettis (11/26/2008)
Good article, but let's take another approach and see what we see.


Heh... you beat me to it, again. Wink


Heh... I may be old, but I'm also trainable.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Phil Factor
Phil Factor
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7504 Visits: 3064
I like both approaches to the problem. They both have a refreshing spark of originality.

I'm not entirely sure if I completely agree with all Hugo's objections, though I share his caution. 'Quirky Update' techniques can go wrong if you are not entirely aware of certain 'gotchas', but they were documented, used, and supported even before Microsoft bought the product. For the 'quirky update' approach that Lynne uses to work safely, you have to remember certain rules (e.g. updates are done in the order of the clustered index, all '@var=col=expression' variable assignments are done before any simple column updates-I hope I've remembered that the right way around!)


Best wishes,

Phil Factor
Simple Talk
Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27362 Visits: 12737
Lynn Pettis (11/27/2008)
Since the purpose of the identity field was order, if there was a gap it wouldn't have caused an issue.


Hi Lynn,

But you are using the IDENTITY values for more than just imposing an order. You use "WHERE B.ID BETWEEN A.ID - 4 AND A.ID" to find the current and four preceeding rows - but if there are gaps, then less than four preceeding rows will match this clause.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27362 Visits: 12737
Jeff Moden (11/27/2008)
Hugo Kornelis (11/27/2008)
What I was refering too is the lack of ROW_NUMBER() in SQL Server 2000. This means you'll either have to take your chance with IDENTITY, at the risk of gaps, as the author of this article did; or you have to use a correlated subquery to calculate the row number on the fly, which can result in dramatic performance as the amount of rows grows. Plus, the queries tend to get long and hard to understand.


Nope... in SQL Server 2000, just do a SELECT INTO a temp table with the IDENTITY function and the author's code works just fine without any difficulty for length or understanding.


Hi Jeff,

You're right. When using SELECT INTO a temp table with the IDENTITY function (*), then there will not be any gaps and the range of A.ID-4 up to A.ID will always have 5 rows. But unless I overlooked something, this was not in the article. To me, the article appears to imply that any IDENTITY row can be used for this. And since many tables already have an IDENTITY column, often with gaps in the sequence due to deleted data or rolled back inserts, I thought it'd be better to point out this danger.

(*) There is another potential problem here. I know that there is only one situation where Microsoft guarantees that identity values are given out in the expected order when using ORDER BY in an INSERT statement, but I can never recall if this guarantee is for SELECT INTO with the IDENTITY() function, of for INSERT ... SELECT on a table with a predefined IDENTITY column. And I can never find this particular bit of documentation when I need it. I think that SELECT INTO with the IDENTITY() function is the supported scenario, but if you are going to use this in production you'd probably better doublecheck first, for my memory is known to .... aaahh, what was I going to say again? Smile


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