Using Temporary tables

Question

Using Temporary tables

raja_saminathan

SSCommitted

Points: 1607
More actions
May 3, 2008 at 12:19 am

#380306

Hi All,
I have a Table with 3 columns
SalesPID SalesAmount DiscountAmount
12 120 20.00
13 130 30.00
14 140 40.00
I Need An Output Table like the Following table which contains extra column with name RunningTotals which is sum of currentRow discountAmount and Previous row of RunningTotal Value.Is it Possible to fill a temporary table or tableVariable or commonTableExpressions or using any other statements without using cursor or looping statements.
SalesPID SalesAmount DiscountAmount Running Totals
12 120 20.00 20.00
13 130 30.00 50.00
14 140 40.00 90.00
Awaiting Your Valuable Inputs
Thanks and Best Regards,
Rajesh

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

I don't suppose you did a search on "running total" or anything like that... 😉

There's a lot of ways to solve the running total problem in SQL Server... they're all slow in the face of any scalability... except for one... see the following URL.

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

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

Eichpeel Ten Centuries Points: 1079 More actions · Answer 2

Jeff' link is really thorough and provides a great explanation of the pros/cons. In the meantime, here is a simple way to solve it. Hope this helps.

declare @table table

(

salespid int

,salesamount money

,discountamount decimal

)

insert into @table select 12, 120, 20.00

insert into @table select 13, 130, 30.00

insert into @table select 14, 140, 40.00

select

outside.salespid

,outside.salesamount

,outside.discountamount

,(

select

sum(inside.discountamount)

from

@table inside

where

inside.salespid <= outside.salespid

) as runningtotal

from

@table outside

order by

3

"Any fool can write code that a computer can understand. Good programmers write
code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999