Query to Calculate Cumulative Salary

Question

Query to Calculate Cumulative Salary

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

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

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

Santhosh (12/23/2008)
vbandlamudi (12/23/2008)
Correct Answer
Select id,name, sal, cumsal=
(select sum(t1.sal)
from employee_san t1
where t1.id <= t.id)
from employee_san t
This works fine.
But the salary is not ORDERed BY ASC

It works fine for only a small number of rows. It is a performance time bomb waiting to happen and they will remember you for it. Don't use it. See the following post for why and test it on 20,000 rows to see how long it takes... it get's worse exponentially...

[font="Arial Black"]Hidden RBAR: Triangular Joins[/font][/url]

--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: 1004340 More actions · Answer 2

r.hensbergen (12/23/2008)
UPDATE #Results
SET @PrevCum_Sal = Cum_Sal = Sal + @PrevCum_Sal,
@PrevName = Name --Just an "anchor"
FROM #Results WITH(INDEX(0))
--===== Display the final result
SELECT Name, Sal, Cum_Sal
FROM #Results
ORDER BY Cum_Sal
How is that Update #Results statement working here?
Thanks
Yes, it also wondered me how powerful this update statement is. I didn't do any testing on it, but is it correct that with this kind of code
SET @a = A + @a
@b-2 = Code
FROM #Test
sums up the A column in the @a variable as long as the Code column stays the same?

Please read the following for answers to these questions...

[font="Arial Black"]Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/font][/url]

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

vbandlamudi Mr or Mrs. 500 Points: 542 More actions · Answer 3

vbandlamudi (12/23/2008)

--------------------------------------------------------------------------------

Correct Answer

Select id,name, sal, cumsal=

(select sum(t1.sal)

from employee_san t1

where t1.id <= t.id)

from employee_san t

the above query took 16 seconds to process 27170 rows

and it took 51 seconds to process 50060 rows.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

vbandlamudi (12/23/2008)
vbandlamudi (12/23/2008)
--------------------------------------------------------------------------------
Correct Answer
Select id,name, sal, cumsal=
(select sum(t1.sal)
from employee_san t1
where t1.id <= t.id)
from employee_san t
the above query took 16 seconds to process 27170 rows
and it took 51 seconds to process 50060 rows.

You don't see anything wrong with that, do you? 16 seconds to calculate only 27170 rows? And the processing rate went down by half when you nearly doubled the number of rows? You really should read the articles I wrote both on your method, which uses a triangular join, and the method I used. The method I used will solve the running total in 7 seconds or less... on a MILLION rows.

Here're the articles again...

[font="Arial Black"]Hidden RBAR: Triangular Joins[/font][/url]

[font="Arial Black"]Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/font][/url]

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

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 5

Please read the following for answers to these questions...
[font="Arial Black"]Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/font][/url]

I'm still too attached to my VB way of thinking, so @Variable = column = formula should in my head mean that @Variable is defined as a boolean and that it's true when column and formula show the same value. Your article didn't 100% clarify that for me.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

r.hensbergen (12/23/2008)
Please read the following for answers to these questions...
[font="Arial Black"]Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/font][/url]
I'm still too attached to my VB way of thinking, so @Variable = column = formula should in my head mean that @Variable is defined as a boolean and that it's true when column and formula show the same value. Your article didn't 100% clarify that for me.

No... it's not boolean. It's an accumulator that contains the current running total (in this case) and it replaces a loop.

In VB, you would start @variable at 0, read a line, add it's amount to the variable, and then save that variable on that line as the running total. That's exactly what the following is doing...

UPDATE sometable

SET @variable = RunningTotalColumn = AmountColumn + @variable

FROM sometable WITH(INDEX(0))

The WITH(INDEX(0)) "hint" (imperative, really, and you can use a named clustered index but it must be clustered), forces the update to follow the order of the clustered index. If it makes you feel better, you can add some complicated derived table to use an order by, but it won't actually be used by the optimizer.

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