Running Totals

Question

Running Totals

wizard5203

SSC Enthusiast

Points: 172
More actions
November 5, 2009 at 1:43 pm

#137061

I want to use a script or stored procedure to capture running totals per employee.
Basically Col 1 is the employee number, column 2 is the hours, column 3 is the total for that day per employee.
See attachment.
Thanks for any help or pointers.

Viewing 15 posts - 1 through 15 (of 20 total)

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Thanks for posting the data example but most folks on this forum like to actually test their code with your data before they post an answer. This running total problem is actually a very simple thing to accomplish but, like I said, folks want to test their code. Take a peek at the article at the first link in my signature below. Post the table creation statment and the data in the readily consumable format it describes and people will be on your problem like white on rice. 😉

Also remember that not all folks will have something that can read an RTF file... it's best if you stick with a simple TXT file.

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

aravind.s SSC-Addicted Points: 449 More actions · Answer 2

Hi.

Try this

Select a.col1,a.col2,(select sum(hrs) from table where emp_id <=a.emp_id) ‘col3’

From table a

assuming empid is your key to the table and You want the running total of HRS column.

wizard5203 SSC Enthusiast Points: 172 More actions · Answer 3

Jeff, thanks for the reply. You could tell from the post I'm a newbie. I looked at your link and will follow it on the next post. Thanks again.

wizard5203 SSC Enthusiast Points: 172 More actions · Answer 4

Grashopper, thanks for the post. Going to try it and then follow the advice and post my code.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Hi

Jeff points to this article:

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

@aravind

Be careful with these kind of statement. This is a triangular join which creates a Cartesian product.

Greets

Flo

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

First, watch SSC for Jeff's rewritten article on Running Totals. IIRC, it is coming out on 11/10/2009. It will be extremely enlightening.

With that, here is a another solution (albiet missing aspects that I'm sure Jeff will elaborate on):

create table dbo.EmpTime (

empid int,

emptime decimal(4,1),

emprunningtime decimal(6,1) null

);

create clustered index IX_EmpTime on dbo.EmpTime (

empid,

emptime

);

insert into dbo.EmpTime (empid, emptime)

select 1, 1.0 union all

select 1, 10.0 union all

select 1, 10.5 union all

select 1, 9.0 union all

select 1, 12.5 union all

select 2, 8.5 union all

select 2, 10.0 union all

select 2, 12.5 union all

select 2, 8.0

;

select

empid,

emptime,

emprunningtime

from

dbo.EmpTime

;

declare @bal decimal(6,1),

@empid int;

set @bal = 0.0;

update dbo.EmpTime set

@bal = emprunningtime = case when @empid <> et.empid then 0.0 else @bal end + et.emptime,

@empid = et.empid

from

dbo.EmpTime et

;

select

empid,

emptime,

emprunningtime

from

dbo.EmpTime

;

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

First of all, the clustered index is required for this to work. I also believe that Jeff will tell you to put a TABLOCKX hint on dbo.EmpTime in the FROM clause.

I am looking forward to his rewrite on this subject, as it actually formed the inspiration for the second article i had written for SSC published eariler this year.

wizard5203 SSC Enthusiast Points: 172 More actions · Answer 8

wizard5203

SSC Enthusiast

Points: 172

November 6, 2009 at 12:30 pm

#1076083

Thank you Lynn, this looks good.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

aravind.s (11/5/2009)
Hi.
Try this
Select a.col1,a.col2,(select sum(hrs) from table where emp_id <=a.emp_id) ‘col3’
From table a
assuming empid is your key to the table and You want the running total of HRS column.

As Flo indicated, be careful with triangular joins. They can actually be worse than a cursor when it comes to scalability. Be sure to read Jeff Moden's article on Running Totals when it is republished next week.

wizard5203 SSC Enthusiast Points: 172 More actions · Answer 10

wizard5203

SSC Enthusiast

Points: 172

November 6, 2009 at 12:34 pm

#1076085

I will read it, thanks for the heads up.

wizard5203 SSC Enthusiast Points: 172 More actions · Answer 11

Lynn, thanks for the post. I will read Jeff's article when it comes out.

Sorry for the double post. Forgot to look for the second page.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 12

aravind.s (11/5/2009)
Hi.
Try this
Select a.col1,a.col2,(select sum(hrs) from table where emp_id <=a.emp_id) ‘col3’
From table a
assuming empid is your key to the table and You want the running total of HRS column.

BE VERY CAREFUL! That's a certain form of "Death By SQL" known as a "Triangular Join" and it's performance can actually be MILLIONS of times worse than a cursor. Please see the following article for more information on why Triangular Joins are so bad for performance... the method is actually RBAR on Sterioids!!! 😉

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Jeff Moden (11/6/2009)
...

What? No comments on the sample code I provided? I really thought you'd have more to add. 😉

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 14

Lynn Pettis (11/6/2009)
Jeff Moden (11/6/2009)
...
What? No comments on the sample code I provided? I really thought you'd have more to add. 😉

I'm not done posting on this thread yet... 😉

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