Calculate values for TallyDuration

Question

Calculate values for TallyDuration

kevin_nikolai

SSCarpal Tunnel

Points: 4343
More actions
May 5, 2010 at 1:10 pm

#234658

create table #MyTest (
Duration int,
EndDuration
);
insert into #MyTest (
Duration,
EndDuration
)
select 50 union all
select 23 union all
select 2 union all
select 3 union all
select 14 union all
select 2
-----------------------------------------
select * from #MyTest
Duration TallyDuration
50NULL
23NULL
2NULL
3NULL
14NULL
2NULL
-----------------------------------------
Have no idea how to do this, any help would be appreciated.
Below is an example of the end result, require T-SQL code/script to achieve result.
Duration is in minutes. Starting Duration is (1) minute
-------------------------
1st entry in Duration field (50) + Starting Duration (1) = TallyDuration (51)
Duration TallyDuration
5051
-----------------------------------------
TallyDuration (51) + 2nd entry in Duration field (23) = 74
Duration TallyDuration
5051
2374
-----------------------------------------
TallyDuration (74) + 3rd entry in Duration field (2) = 76
Duration TallyDuration
5051
2374
276
-----------------------------------------

Viewing 8 posts - 1 through 7 (of 7 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

This is usually referred to as a "Running Total". See the link in my signature for a wonderful article on how to solve this issue.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Clive,

First, thanks for posting the test setup... makes it so I can concentrate on your problem and give you a coded answer almost as soon as I see the post.

See the article that Seth pointed out. It's a long article on some very simple code but every point is important. The rules for using the code are quite simple but if you leave one out, you take a hell of a chance on getting the wrong answers. Here's the code... as normal, I explain pretty much everything in the comments. I believe you'll recognize your formula... you had the right idea...

--===== Create the test table as before, but with some important additions.

-- Note the IDENTITY column with the quintessential CLUSTERED index.

CREATE TABLE #MyTest

(

SortOrder INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

Duration INT,

TallyDuration INT

)

;

--===== Populate the test table exactly the same as before

INSERT INTO #MyTest

(

Duration

)

SELECT 50 UNION ALL

SELECT 23 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 14 UNION ALL

SELECT 2

;

--===== Solution to the problem starts here...

-- First, declare some variables that we need for the running total

-- and preset the 1 minute mark.

DECLARE @RunningTotal INT,

@Anchor INT

;

SELECT @RunningTotal = 1

;

--===== Now, everything becomes simple. Do the running total

UPDATE #MyTest

SET @RunningTotal = TallyDuration = @RunningTotal + Duration,

@Anchor = SortOrder

FROM #MyTest WITH (TABLOCKX) --For additional speed

OPTION (MAXDOP 1) --Prevents parallelism which would destroy this process

;

--===== Display the result

SELECT * FROM #MyTest ORDER BY SortOrder

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Clive... how'd that work for you?

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

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 4

Hi Jeff, thanks, you're a genius. I also had a look at some of your other articles:

http://www.sqlservercentral.com/articles/T-SQL/62867/

The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

http://www.sqlservercentral.com/articles/T-SQL/68467/

Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

http://www.sqlservercentral.com/articles/T-SQL/63681/

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

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

Heh... I just wanted to know if it worked for you. :blush: Thanks for the compliment, Clive.

Just a followup warning... don't take any shortcuts or deviate from the rules on the running total method I posted. The rules of usage (at the end of the running total article) are simple but strict. Deviation from those rules will someday cause an error depending on what the data is, etc, etc.

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

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 6

Hi Jeff, it worked like a charm. Not to mention that the code is not complicated or cumbersome,

yet extremely effective. Thanks again.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

You bet... thanks for the feedback, Clive. 🙂

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