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»»»

Calculate the Running Total for the last five Transactions Expand / Collapse
Author
Message
Posted Thursday, November 27, 2008 7:40 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, November 11, 2013 2:42 AM
Points: 150, 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

Post #609812
Posted Thursday, November 27, 2008 8:11 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,303, Visits: 1,378
Very good point pointed out by Mike. It will be never zero.


Post #609836
Posted Thursday, November 27, 2008 8:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 21,385, Visits: 9,603
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!
Post #609863
Posted Thursday, November 27, 2008 9:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:50 PM
Points: 23,045, Visits: 31,570
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 --





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)
Post #609897
Posted Thursday, November 27, 2008 11:00 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(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 #609925
Posted Thursday, November 27, 2008 11:02 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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. ;)


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

(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 #609926
Posted Thursday, November 27, 2008 11:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:50 PM
Points: 23,045, Visits: 31,570
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. ;)


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



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)
Post #609930
Posted Thursday, November 27, 2008 1:06 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 577, Visits: 2,503
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
Post #609963
Posted Thursday, November 27, 2008 1:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 5,926, Visits: 8,175
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
Post #609973
Posted Thursday, November 27, 2008 1:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 5,926, Visits: 8,175
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? :)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #609976
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse