SQL Server 2000

[Magosoft]

I want to do this
Doc. Date IN OUT Balance
001 01/01/2001 1200 1200
002 01/01/2001 300 800
003 01/01/2001 400 400
004 01/01/2001 150 550

I want to get the Balance Column directly in a SQL Sentence.

Is this possible?

I will appreciate any help.

Tank You

Mago

 

[grahamm]

You request is not clear. Could you elaborate. To project out the balance column, the SQL is for example:

select Balance from Ledger

However I expect this is not what you mean.

 

[tlbroadbent]

This script works for smaller tables - up to a few hundred records. It can be slow for thousands of records.

Select
Doc, TranDate, AmtIn, AmtOut,
Balance=(Select sum(AmtIn-AmtOut) From table1 Where Doc<=t.doc And TranDate<=t.TranDate)
From table1 As t

The next script will be faster for larger numbers of records.

Create table #tmp (Doc int, TranDate datetime, AmtIn int, AmtOut int, Balance int)

Insert #tmp
Select Doc, TranDate, AmtIn, AmtOut, 0
From #table1

Declare @balance int
set @balance=0

Update #tmp
Set @balance=Balance=@balance+AmtIn-AmtOut

Select * From #tmp

Drop table #tmp Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.