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: 150468 More actions · Answer 1

Jeff Moden (11/19/2010)
beautiful

:blush:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

WayneS SSC Guru Points: 95460 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: 430 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: 95460 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: 1004407 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)

WayneS SSC Guru Points: 95460 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