Calculating a field. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculating a field.

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-06 : 10:34:09

I was going to ask the question first but then thought on past experience I best do it this way.

Below is the code to test this out with if someone would be so kind.

I do not just yet want the exact answer to solve my problem as I won't learn that way, but if someone could point me in the right direction and allow me to try to suss it?

OK. I have a table with a 2 columns in it called movement_qty and rolling_balance.

I have used a select staement to populate the table whereby all fields are filled except rolling_balance.

I now want to go through the table and calculate the rolling_balance figure by moving through each row in the table and do the following:

rolling_balance = rolling_balance + movement_qty. Obviously it needs to keep track of the previous rows rolling_balance figure.

I could do this in VB.NET / VBA easier enough but don't know how here. I tried a update statement with no joy.

Here is the code for testing;

DECLARE @Transactions TABLE(movement_qty decimal(20,8),rolling_balance decimal(20,8))

insert into @Transactions 440, 0
insert into @Transactions 5, 0
insert into @Transactions 883, 0
insert into @Transactions -10, 0
insert into @Transactions 4, 0
insert into @Transactions 23, 0
insert into @Transactions -700, 0

So the end result would be;

440, 440
5, 445
838,1283
-10, 1273
4, 1277
23, 1300
-700, 600

Like I say, if someone can POINT me in the right direction I will gladly do the rest.

Thnx

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-06 : 10:40:59

LOL

use the articles section and look for "running totals".

i am laughing my ass off as i'm writing this...

Go with the flow & have fun! Else fight the flow

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-06 : 10:43:01

Why because of my tippy toe approach, or cos it is so stupidly simple I will kick myself

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-06 : 10:43:36

by the way... your record with need some sort of field to distinguish the order in which the records should be considered/displayed. (possible a createdDate? or an autoId?)

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-06 : 10:44:58

no... i think it was more of the way you were trying to be as clear as possible and provide all of the necessary DDL/DML, but you forgot to look in the articles

Its ok... at least you are making the effort...

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-06 : 10:46:08

actually i was picturing you and your wife looking for articles section...

ROTFLOL!!!!

Go with the flow & have fun! Else fight the flow

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-06 : 10:51:08

Well here is something else for you to laugh at, cos I am! I did look at the articles but searched for, loop, while, for..next etc! Gee now why didn't I think of....running totals???

Corey, the real table has loads of fields in it two of which are date and tran-id. I left them out here as I presumed the data I gave for the test was sufficient. LOL

hey, at least we are all laughing now

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-06 : 11:03:07

generally, a sample case should have some sort of key or identifier (it is almost always relavant)

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-07 : 04:41:11

OK I have been reading about cursors and although I do get some results they are duplicated, not with running totals?

I read that you don't have to call all fields when using a cursor, is this right?

Presume variables have been declared..

For example if my table is like so:

create table @mytable (dated datetime, id int, movement decimal(20,8), rolling decimal(20,8), user varchar(30), location varchar(10), costed decimal(20,8))

is this allowed?

DECLARE crs_Transactions CURSOR
FOR
SELECT dated, id, movement, rolling
FROM @Transactions
ORDER BY dated, id

FETCH NEXT FROM crs_Transactions INTO @Dated, @TranId, @MovementQty, @RollingBalance

WHILE @@FETCH_STATUS = 0
BEGIN
SET @RollingBalance = @RollingBalance + @MovementQty

INSERT @Transactions VALUES (@Dated,@TranId,@MovementQty,@RollingBalance)

FETCH NEXT FROM crs_Transactions INTO @Dated,@TranId,@MovementQty,
@RollingBalance
END

I get an error message stating:

Insert Error: Column name or number of supplied values does not match table definition.

If I include all the columns I get results, but incorrect ones.

At present I dont want you guys to spend your time trying to replicate my error, rather just to let me know have I misunderstood the usage of cursors.

I want to continue to try to figure it out so perhaps another pointer, please?

Thnx

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-07 : 04:57:02

rule no. 1: DON'T USE CURSORS!!!!

rule no. 2:
read http://www.sqlteam.com/item.asp?ItemID=3856
and use solution 2 or 3 not 1.

ok?

Go with the flow & have fun! Else fight the flow

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-07 : 05:01:26

NOW I AM CONFUSTICATED!!

I read this topic yesterday and listened to this bit of advice..

Solution 1 uses a temporary table and a cursor and executes extremely fast. I must admit that my original intent was to show this solution as the way *not* to solve the problem. In general, I tell developers to avoid cursors at all costs. In this particular case, however, the cursor approach is much quicker than the single SELECT solutions

As my resultset will small and the results in that test were quicker I thought that was the way to go?

So, are you saying at all costs NEVER use cursors?

Stupido question, if they are so bad.....why are they available?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-07 : 05:08:19

ok never is a hard word to use....
better to use them only when all else fails

your cursor is faster? i guess that's posible too...

have you indexed the table properly?

why are cursors present? probably because not everything can be solved set based.
but somewhere along the way they got to be used by people who were used to procedural
code for tasks they weren't meant for.

Go with the flow & have fun! Else fight the flow

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-07 : 05:31:25

I don't have an index on the @Transaction table I create no. I don't know if the cursor is faster or not I was only going on the example results sets given which showed solution 1 as being faster.

I will have a go now at solution 2

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-07 : 05:56:24

Panic mode...system overload!!

I do not understand either solution 2 or 3 and think they wont work with my problem as my key fields are a date and a transaction id where the transaction id can be smaller or greater than those of previous days.

Is it time for me to post ddl and data?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-07 : 06:20:26

the time was yesterday my friend... yesterday

see this is what i call sarcasm

Go with the flow & have fun! Else fight the flow

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-07 : 07:03:44

OK, I hope this is suffiecient...

DECLARE @Transactions TABLE(curr_date datetime, tran_id int, tran_type char(1), tran_status char(1), tran_no int, movement_qty decimal(20,8), rolling_balance decimal(20,8), tran_mtrl_cost decimal(20,8), user_nm varchar(30), location varchar(10), part_no varchar(30))

INSERT INTO @Transactions(curr_date, tran_id, tran_type, tran_status, tran_no, movement_qty, rolling_balance, tran_mtrl_cost, user_nm, location, part_no)
SELECT '20/08/2004', '22062', 'I', '22065', 7, 0, 3.57,'JPL', 'UK','VIDO23'
SELECT '20/08/2004', '22435', 'I', '22438', 20, 0, 10.2,'JPL', 'UK','VIDO23'
SELECT '20/08/2004', '23879', 'I', '23882', 50,0, 24.5,'MPL', 'UK','VIDO23'
SELECT '20/08/2004', '23889', 'I', '24889', -45, 0,-22.5,'JPL', 'UK','VIDO23'
SELECT '29/08/2004', '879', 'P', '1789', 10, 0,4.00, 'KKJ','UK','VIDO23'
SELECT '15/09/2004', '28899', 'I', '22356', -700,0, -550.00, 'PHU', 'UK',,'VIDO23'
SELECT '16/10/2004', '78258', 'S', '483', -50,0, -68.00,'JPL', 'UK','VIDO23'
SELECT '20/11/2004', '33898', 'U', '22238', 12,0, 4.50,'JPL', 'UK','VIDO23'

Basically I am trying to return the complete contents of this table with the roling_balance field populated with the running total of movement_qty.

The start value for rolling_balance will be the first value of movement_qty.

If any one can resolve this could I ask that you supply some good comments to explain how you do it.....and I will place you at the top of my xmas card list....promise

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-07 : 07:56:00

Hog
Couple of amendments to your DDL, also i have had to set your trans_stats to '' as you hadnt provided any data for it and was missing fromyour select


SET DATEFORMAT dmy
DECLARE @Transactions TABLE(curr_date datetime, tran_id int, tran_type char(1), tran_status char(1), tran_no int, movement_qty decimal(20,8), rolling_balance decimal(20,8), tran_mtrl_cost decimal(20,8), user_nm varchar(30), location varchar(10), part_no varchar(30))

INSERT INTO @Transactions(curr_date, tran_id, tran_type, tran_status, tran_no, movement_qty, rolling_balance, tran_mtrl_cost, user_nm, location, part_no)
SELECT '20/08/2004', '22062', 'I', '', '22065', 7, 0, 3.57,'JPL', 'UK','VIDO23' UNION ALL
SELECT '20/08/2004', '22435', 'I',  '', '22438', 20, 0, 10.2,'JPL', 'UK','VIDO23' UNION ALL
SELECT '20/08/2004', '23879', 'I', '',  '23882', 50,0, 24.5,'MPL', 'UK','VIDO23' UNION ALL
SELECT '20/08/2004', '23889', 'I', '',  '24889', -45, 0,-22.5,'JPL', 'UK','VIDO23' UNION ALL
SELECT '29/08/2004', '879', 'P', '',  '1789', 10, 0,4.00, 'KKJ','UK','VIDO23' UNION ALL
SELECT '15/09/2004', '28899', 'I', '',  '22356', -700,0, -550.00, 'PHU', 'UK','VIDO23' UNION ALL
SELECT '16/10/2004', '78258', 'S', '',  '483', -50,0, -68.00,'JPL', 'UK','VIDO23' UNION ALL
SELECT '20/11/2004', '33898', 'U', '',  '22238', 12,0, 4.50,'JPL', 'UK','VIDO23'


SELECT * FROM @Transactions

Andy

Beauty is in the eyes of the beerholder

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-07 : 08:50:39

you don't have a unique id to sort it on so i added it.


SET DATEFORMAT dmy
DECLARE @Transactions TABLE(id int identity(1,1), curr_date datetime, tran_id int, tran_type char(1), tran_status char(1), tran_no int, movement_qty decimal(20,8), rolling_balance decimal(20,8), tran_mtrl_cost decimal(20,8), user_nm varchar(30), location varchar(10), part_no varchar(30))

INSERT INTO @Transactions (curr_date, tran_id, tran_type, tran_status, tran_no, movement_qty, rolling_balance, tran_mtrl_cost, user_nm, location, part_no)
SELECT '20/08/2004', '22062', 'I', '', '22065', 7, 0, 3.57,'JPL', 'UK','VIDO23' UNION ALL
SELECT '20/08/2004', '22435', 'I',  '', '22438', 20, 0, 10.2,'JPL', 'UK','VIDO23' UNION ALL
SELECT '20/08/2004', '23879', 'I', '',  '23882', 50,0, 24.5,'MPL', 'UK','VIDO23' UNION ALL
SELECT '20/08/2004', '23889', 'I', '',  '24889', -45, 0,-22.5,'JPL', 'UK','VIDO23' UNION ALL
SELECT '29/08/2004', '879', 'P', '',  '1789', 10, 0,4.00, 'KKJ','UK','VIDO23' UNION ALL
SELECT '15/09/2004', '28899', 'I', '',  '22356', -700,0, -550.00, 'PHU', 'UK','VIDO23' UNION ALL
SELECT '16/10/2004', '78258', 'S', '',  '483', -50,0, -68.00,'JPL', 'UK','VIDO23' UNION ALL
SELECT '20/11/2004', '33898', 'U', '',  '22238', 12,0, 4.50,'JPL', 'UK','VIDO23'


SELECT 	id, movement_qty, rolling_balance  = (select SUM(movement_qty) from @Transactions where id <= t1.id),
	curr_date, tran_id, tran_type, tran_status, tran_no, tran_mtrl_cost, user_nm, location, part_no
FROM 	@Transactions t1

Go with the flow & have fun! Else fight the flow

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-07 : 09:23:46

Thanks to you both, sorry about the missing data

I will plumb this in and see what happens and get back to you....

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-07 : 09:40:55

Hog -- the main question is: do you need to do this in SQL? What are you doing with the results of this query? Are you returning it to the users and displaying it in an application or on a report?

It is often much, much easier and more efficient to calculate totals, groups, and running totals at the client side or presentation layer than it is to do it in SQL Server. You should only calculated running totals and such in SQL if you need the results of those calculations for further processing within SQL itself. Otherwise, just return the raw data the client needs and allow for the client to do any totalling it desires.

Also, remember that in databases, there is no inherit concept of ordering in your tables. When you want to calculate a running total, or anything involving "the last row or the next row" in a table, you need to be able to unquely identify each row in your table by a set of columns, and you must specify the exact ordering desired to calculate your results.

i.e., in SQL you would never say "running total from the previous rows", but rather you'd say "the total of all rows in which the transaction date is less than the current transaction date". Things need to be conceptualized and phrased in terms of the data itself, not based on the "position" of that data within a table or even a resultset.

- Jeff

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-07 : 09:46:40

Spirit1, it works a treat until it comes across the usual crap way our system records data which makes no frickin sense to me, and makes me angry that I am continually having to ram square pegs in round holes just to get correct answer from my SQL.

Jeff, I would not know how to go about what you say. The setup here is users have this report tool. The reports are based on view/stored proc created by our supplier, but more useful ones created by me for. Therefore for this report the user enters the part number and hits return. My proc reads that part number and gets all the transaction data. I then want to calculate the rolling balance. See my problem above in message to spirit1.

I wish there was an easy way for me to send you a snapshot of the original data and schemas but I truly do not know how to do this

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources