another sql query problem - 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)

another sql query problem

Author

Topic

bashka_abdyli
Starting Member

15 Posts

Posted - 2003-07-15 : 07:29:26

I have a table like this

DESCRIPTION DEBI CREDIT
-----------------------------------
Deposit 0.00 100.00
Withdraw 10.00 0.00
Payroll 0.00 230.00
Withdraw 50.00 0.00

How can i make an query that this table look like this

ID DESCRIPTION DEBI CREDIT Current Balance
---------------------------------------------------------
1. Deposit 0.00 100.00 100.00
2. Withdraw 10.00 0.00 90.00
3. Payroll 0.00 230.00 320.00
4. Withdraw 50.00 0.00 270.00

Please help

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-15 : 08:03:41

Hi!

You'll need some kind of order to your transactions, for example a valuedate or timestamp for the transactions. I'll show you using a date column:

create table transactions
(
valuedate datetime,
description varchar(20),
debi decimal(20,2),
credit decimal(20,2)
)

insert transactions
values ('20030701', 'Deposit', 0, 100)

insert transactions
values ('20030702', 'Withdraw', 10, 0)

insert transactions
values ('20030703', 'Payroll', 0, 230)

insert transactions
values ('20030704', 'Deposit', 50, 0)

OK that's the data... Now for the query:

select valuedate,
description,
debi,
credit,
(select sum(credit)-sum(debi) from transactions t2 where t2.valuedate <= t1.valuedate) as [current balance]
from transactions t1

bashka_abdyli
Starting Member

15 Posts

Posted - 2003-07-15 : 08:07:04

yes thats correct but my table has two ore more transaction with the same value date.

bashka_abdyli
Starting Member

15 Posts

Posted - 2003-07-15 : 08:09:40

and it is very important to have the ID column 1,2,3,4

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-15 : 08:11:31

quote:

and it is very important to have the ID column 1,2,3,4

Then you need to decide by which logic the ID is assigned. For example, you say that your tranactions have the same date, well, then, how do we know which one of those transactions should be number 2 and which number 3?

-------
Moo. :)

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-15 : 08:12:05

Well... Maybe you have some kind of timestamp or other identifier which you can sort the transactions by? This is the normal procedure for banks etc. You need to be able to see which transaction occured first.

If you use my query and have the same dates, you will get the correct current balance for the end of that day.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-15 : 08:13:57

The ID was not in the table you specified in the original post, which is why I didn't include it.

If you have a column called ID, which specifies the order of the transactions, just change the query to:

select ID,
valuedate,
description,
debi,
credit,
(select sum(credit)-sum(debi) from transactions t2 where t2.ID <= t1.ID) as [current balance]
from transactions t1

dsdeming

479 Posts

Posted - 2003-07-15 : 08:17:41

I've tweaked the code to add in the id column and to put in dates with a time component of something other than midnight. Note the 2-second delay between inserts.

create table transactions
(
id int,
valuedate datetime,
description varchar(20),
debi decimal(20,2),
credit decimal(20,2)
)

insert transactions
values (1, getdate(), 'Deposit', 0, 100)

waitfor delay '000:00:02'

insert transactions
values (2, getdate(), 'Withdraw', 10, 0)

waitfor delay '000:00:02'

insert transactions
values (3, getdate(), 'Payroll', 0, 230)

waitfor delay '000:00:02'

insert transactions
values (4, getdate(), 'Deposit', 50, 0)

select id, valuedate,
description,
debi,
credit,
(select sum(credit)-sum(debi) from transactions t2 where t2.valuedate <= t1.valuedate) as [current balance]
from transactions t1

Dennis

bashka_abdyli
Starting Member

15 Posts

Posted - 2003-07-15 : 08:21:50

OK MY TABLE is this here is a sample:

Transaction ID VALUE DATE DESCRIPTION DEBIT CREDIT BRANCH

12 01/01/2003 deposit 0.00 50.00 PR
7 01/02/2003 withdraw 10.00 0.00 MI
19 01/05/2003 deposit 0.00 150.00 PR
21 01/05/2003 deposit 0.00 120.00 PR

Each Branch has it TRANSACTION ID counter

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-15 : 08:25:29

Well you still have the problem of identifying the order of the transactions. You need a timestamp or something like that to get the order right. Otherwise, it would look like the customers can withdraw money which is not there, etc.

bashka_abdyli
Starting Member

15 Posts

Posted - 2003-07-15 : 08:28:05

I use this ORDER BY:

order by data_val, brloc, transactionID

bashka_abdyli
Starting Member

15 Posts

Posted - 2003-07-15 : 08:36:18

There is a possibility to make this with FETCH but i get a result like this with these FIELD NAMES are in EVERY ROW and with (1 row(s) affected) in every row.

dummycolmn company
----------- ----------------------------------------------------------
1 Alfreds Futterkiste

(1 row(s) affected)

dummycolmn company
----------- ---------------------------------------------------------
2 Ana Trujillo Emparedados y helados

(1 row(s) affected)

dummycolmn company
----------- ----------------------------------------------------------
3 Antonio Moreno Taquería

(1 row(s) affected)

dummycolmn company
----------- ---------------------------------------------------------
4 Around the Horn

(1 row(s) affected)

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-15 : 08:45:08

Well do what you want. Using a cursor will enable you to get false negative balances, but go ahead, i don't really care. It's not good design though.

bashka_abdyli
Starting Member

15 Posts

Posted - 2003-07-15 : 08:51:49

Well i know that the structure isn't ok but we have 20 milion rows at our transaction table and it's on replication with 30 branches and i cannot do any major changes before 31 December 2003, so I kindly asked for help.

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-15 : 09:11:14

I think that the point Andraax - and myself - were making is that you need some kind of time reference to be able to correctly order your entries and produce the resultant running balances. Otherwise if there is more than one transaction on any particular day you could see a nonsense figure in the balance column. If this is of no importance to you, then I believe any of the solutions already shown in this thread should suffice.

There are various other ways to do running totals, try a forum search, and there is also an article on them in the articles section.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources