Running Total using T-SQL

Question

Post reply

Running Total using T-SQL

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150457 More actions · Answer 1

Paul White

SSC Guru

Points: 150457

November 19, 2010 at 6:21 am

#1252999

Jeff Moden (11/19/2010)

beautiful

:blush:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95405 More actions · Answer 2

Paul White NZ (11/19/2010)

Jeff Moden (11/19/2010)

beautiful

:blush:

Would you prefer inspired? Elegant? Truly awesome? (BTW: all true!)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ralph L. Wahlert SSC-Addicted Points: 400 More actions · Answer 3

WayneS (11/19/2010)

Paul White NZ (11/19/2010)

Jeff Moden (11/19/2010)

beautiful

:blush:

Would you prefer inspired? Elegant? Truly awesome? (BTW: all true!)

Jeff:

This is absolutely fantasitic!

How would I modify the script in order add the "Site" field to the order? Looking through the output I realized I had forgotten that I need to have the running total reset when the SKU/Site combination changes.

I would also like to thank all of those who contributed to this solution. You are all amazing in your abilities.

Ralph

WayneS SSC Guru Points: 95405 More actions · Answer 4

Ralph L. Wahlert (11/20/2010)

WayneS (11/19/2010)

Paul White NZ (11/19/2010)

Jeff Moden (11/19/2010)

beautiful

:blush:

Would you prefer inspired? Elegant? Truly awesome? (BTW: all true!)

Jeff:

This is absolutely fantasitic!

How would I modify the script in order add the "Site" field to the order? Looking through the output I realized I had forgotten that I need to have the running total reset when the SKU/Site combination changes.

I would also like to thank all of those who contributed to this solution. You are all amazing in your abilities.

Ralph

Just add the Site to the clustered index, and appropriate places in the code. I believe that this will cover it:



--=====================================================================================================================

--      Build some sample data.  This is the way you should do it when you post a question to make it easier on folks.

--      This is NOT a part of the solution but is an essential part to us being able to demo a solution for you.

--=====================================================================================================================

--===== Conditionally drop the test table to make reruns easier

     IF OBJECT_ID('tempdb..#TestData','U') IS NOT NULL

        DROP TABLE #TestData

;

--===== Create and populate the test table on the fly

 SELECT *, CONVERT([numeric](19, 5), NULL) as 'RunningTotal'

   INTO #TestData

   FROM [_PositionShipping] a

;

--===== Put a totally bogus clustered index on the data just to prove this works

 CREATE CLUSTERED INDEX IX_#TestData ON #TestData ([Site], SKU)  --<<<< Add site here

;

--===== Display the test data before we do anything to it

 SELECT * FROM #TestData ORDER BY [Site], SKU, [Required]

;

--=====================================================================================================================

--      This is the solution without the overhead of a Triangular Join.  It'll do a million rows in just seconds.

--=====================================================================================================================

--===== Declare some essential variables with obvious names to reflect their use

DECLARE @PrevItem VARCHAR(100),

        @PrevBal  [numeric](19, 5),

        @Counter  INT,

        @PrevSite CHAR(11)  --<<< Add a variable for the previous site here

;

--===== It's essential to preset the counter variable.  All the others may be calculated during the UPDATE

SELECT @Counter = 1

;

--===== This produces an ordered, running total update.  It has a built in fault detector that will let you know if

     -- a failure occured.  That same fault detector is what makes the ordered update work even when the clustered

     -- index is in a totally different order.  This type of update is affectionately known as the "Quirky Update"

     -- and it's a powerful tool to learn.  Special thanks to Paul White and Tom Thompson for the fault detector.

WITH

cteSort AS

(

 SELECT Counter = ROW_NUMBER() OVER(ORDER BY [Site], SKU, Required),  --<<< add site here

        *

   FROM #TestData

)

 UPDATE tgt

    SET @PrevBal = RunningTotal = CASE WHEN tgt.Counter = @Counter

                                  THEN CASE

                                       WHEN SKU = @PrevItem AND [Site] = @PrevSite  --<<< add site check here

                                            THEN tgt.Ctns + @PrevBal

                                        ELSE tgt.Ctns

                                        END

                                   ELSE 1/0 --Force error if out of sequence

                               END,

        @PrevItem = SKU,

        @PrevSite = [Site],

        @Counter  = @Counter + 1

   FROM cteSort tgt WITH (TABLOCKX) --Absolutely essential, we don't want anyone sneaking in while we're updating

  OPTION (MAXDOP 1)                 --Parallelism must be prevented for the serial nature of this update

;

--===== Display the test data after the running total update

 SELECT * FROM #TestData ORDER BY [Site], SKU, [Required]

;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Thanks for the "cover", Wayne. I haven't had the time to get to the forums much in the last 3 days. We have a problem at work and I've been doing 14 hour days for the last three days and that continues today. I just caught a brief break. Thanks 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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

WayneS SSC Guru Points: 95405 More actions · Answer 6

No problem - always glad to help. I've noticed your absence, and didn't know when you might be back. Haven't heard from the OP, so maybe it wasn't quite as necessary to jump in as I initially thought.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2