cross or outer apply for balance

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 2005 Forums

Transact-SQL (2005)

cross or outer apply for balance

Author

Topic

rekiller
Starting Member

31 Posts

Posted - 2008-11-19 : 17:12:35

Hi, I need help.
I have a 4 fields table like this:


DECLARE @TABLE TABLE
(
	date	datetime,
	account int,
	Debit	int,	
	Credit	int
)

INSERT INTO @TABLE
SELECT	getdate(),100511, 100, 0	UNION ALL
SELECT	getdate(),100511, 100, 0	UNION ALL
SELECT	getdate(),100511, 0, 50		UNION ALL
SELECT	getdate(),100511, 100, 0

SELECT * FROM @TABLE

What i Want is to make a field Balance, for each row.
I have read about cross and outer apply, but examples i fond in this forum, only works with transactions that hast unique key for each transaction.
How can i use here Apply?

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-19 : 20:54:22

select *, balance = (select sum(t2.Credit) - sum(t2.Debit) from tbl t2 where t2.account = t.account and t2.date <= t.date)
from tbl t
order by account, date

Assuming that date is unique per account - otherwise it will give the end of day balance.
If you don't have a unique key then you don't have a relational database.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 01:30:11

using APPLY


SELECT *,cum.TotalCredits-cum.TotalDebits AS Balance FROM @TABLE t
OUTER APPLY (SELECT SUM(Debit) AS TotalDebits,SUM(Credit) AS TotalCredits
             FROM @TABLE
             WHERE account=t.account
             AND date<=t.date)cum

rekiller
Starting Member

31 Posts

Posted - 2008-11-20 : 08:33:59

Yes, but problem here is that Date, in the system sometime is the same, cause it doesnt save time? Do i have to do a rowcount instead of date?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 09:04:14

quote:
Originally posted by rekiller

Yes, but problem here is that Date, in the system sometime is the same, cause it doesnt save time? Do i have to do a rowcount instead of date?

then you can find out only overall balance for a day.
B/w do you have any uniqued valued column in it? may be some identity column?

rekiller
Starting Member

31 Posts

Posted - 2008-11-20 : 09:21:36

Oh, ok, its ok Balance for a Day.
It doesnt matter what row is first or after is the date is the same, so, your solution is good enough.

quote:
Originally posted by visakh16

quote:
Originally posted by rekiller

Yes, but problem here is that Date, in the system sometime is the same, cause it doesnt save time? Do i have to do a rowcount instead of date?

then you can find out only overall balance for a day.
B/w do you have any uniqued valued column in it? may be some identity column?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources