running total

Question

running total

chandreshgeria

Valued Member

Points: 74
More actions
November 20, 2009 at 2:27 am

#212789

Sir ,
I think that what you have given is to lengthy process i give procedure of my way how i calculate running total
-------------------
drop TABLE #data -- drop temp table if exists
CREATE TABLE #data (myctr INT IDENTITY,chrfolioid VARCHAR(20),numgrandtotal NUMERIC(18,2),running NUMERIC(18,2))
INSERT INTO #data (chrfolioid,numgrandtotal)
SELECT chrfolioid ,numgrandtotal
FROM tARHeader t
ORDER BY t.chrFolioID ,t.dteTxnDate
DECLARE @ctr AS INT
DECLARE @running NUMERIC(18,2)
DECLARE @oldfolioid AS VARCHAR(20)
SET @ctr = 1
SET @running = 0
SET @oldfolioid = ''
WHILE @ctr <= (SELECT COUNT(*) FROM #data)
BEGIN
IF @oldfolioid <>(SELECT chrfolioid FROM #data WHERE myctr = @ctr)
BEGIN
SET @running= 0
SET @oldfolioid = (SELECT chrfolioid FROM #data WHERE myctr = @ctr)
END
UPDATE #data SET running = (SELECT SUM(numgrandtotal)
from #data
where myctr <= @ctr and chrfolioid = @oldfolioid
GROUP BY chrfolioid ) WHERE myctr = @ctr
SET @ctr = @ctr + 1
PRINT @ctr
END
SELECT * FROM #data ORDER BY 1,2

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

chandreshgeria (11/20/2009)
Sir ,
I think that what you have given is to lengthy process i give procedure of my way how i calculate running total
-------------------
drop TABLE #data -- drop temp table if exists
CREATE TABLE #data (myctr INT IDENTITY,chrfolioid VARCHAR(20),numgrandtotal NUMERIC(18,2),running NUMERIC(18,2))
INSERT INTO #data (chrfolioid,numgrandtotal)
SELECT chrfolioid ,numgrandtotal
FROM tARHeader t
ORDER BY t.chrFolioID ,t.dteTxnDate
DECLARE @ctr AS INT
DECLARE @running NUMERIC(18,2)
DECLARE @oldfolioid AS VARCHAR(20)
SET @ctr = 1
SET @running = 0
SET @oldfolioid = ''
WHILE @ctr <= (SELECT COUNT(*) FROM #data)
BEGIN
IF @oldfolioid <>(SELECT chrfolioid FROM #data WHERE myctr = @ctr)
BEGIN
SET @running= 0
SET @oldfolioid = (SELECT chrfolioid FROM #data WHERE myctr = @ctr)
END
UPDATE #data SET running = (SELECT SUM(numgrandtotal)
from #data
where myctr <= @ctr and chrfolioid = @oldfolioid
GROUP BY chrfolioid ) WHERE myctr = @ctr
SET @ctr = @ctr + 1
PRINT @ctr
END
SELECT * FROM #data ORDER BY 1,2

Thank you for the feedback and the code... but have you actually tried such code for performance sake?

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

chandreshgeria Valued Member Points: 74 More actions · Answer 2

Sir ,

I have not seen any sake in performance of executing query , can you help it out where I doing Wrong

I tried this statement on a table which having 10 years of data and in each year there is n numbers of transaction in each folio code and there are more then 5000 folioid in in each accounting year ,my server configuration if MSDE setup of SQL server 2000 and system having memory of 2GB with core2DUO CPU

chandresh

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

chandreshgeria (11/22/2009)
Sir ,
I have not seen any sake in performance of executing query , can you help it out where I doing Wrong
I tried this statement on a table which having 10 years of data and in each year there is n numbers of transaction in each folio code and there are more then 5000 folioid in in each accounting year ,my server configuration if MSDE setup of SQL server 2000 and system having memory of 2GB with core2DUO CPU
chandresh

How many rows in total?

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

chandreshgeria Valued Member Points: 74 More actions · Answer 4

sir ,

Depend on party turnover if 2 rows return two rows and if 200000 return 200000 with running total

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

chandreshgeria (11/24/2009)
sir ,
Depend on party turnover if 2 rows return two rows and if 200000 return 200000 with running total

Perhaps you don't understand. I'm trying to setup an example based on what you said so you can see. I asked a simple question... how many rows in total for all 10 years?

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

Jeff,

Wouldn't a million row setup show what you are talking about? Of course, you could make it ten million.

chandreshgeria Valued Member Points: 74 More actions · Answer 7

chandreshgeria

Valued Member

Points: 74

November 24, 2009 at 11:11 pm

#1083815

appx 50000 rows

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 8

Lynn Pettis (11/24/2009)
Jeff,
Wouldn't a million row setup show what you are talking about? Of course, you could make it ten million.

Heh... it absolutely would... but he's using MSDE 2000 and, since it has some limits, I didn't want the test table to be a source of irritation.

--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: 1004311 More actions · Answer 9

chandreshgeria (11/24/2009)
appx 50000 rows

Thank you... I'll be back after work tonight.

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