SQLTeam.com | Weblogs | Forums

Select with calculation of Balance


#1

I have a account list to be produced, which joins a Transactions table (for details of date, description, etc) with a Transactions_Detail table which contains one or more transaction values linked back to a row in the Transactions table.

I need to output this, but with a balance calculated from each transaction, starting with the opening balance.

The code below works but isn't very fast - so I wondered can someone better than me at SQL (not hard!) can show me how to speed this up. Hopefully the code is fairly self-explanatory.

Thanks, Phil

Declare @Account int = 1
Declare @VATEnabled bit = (Select C.[VAT Enabled] From [Accounts] A INNER JOIN [Account Class] C on C.ID = A.Class where A.ID = @Account)
Declare @OpeningBalance Decimal(19,4) = (Select A.[Start Balance] From Accounts A where A.ID = @Account)
Declare @OpeningDate Date = (Select A.[Start Date] From Accounts A where A.ID = @Account)

Declare @Trans Table (ID Int, Reference Varchar(50), [Cheque Number] Varchar(50), [Date] Date, [Description] Varchar(255),
[Category Name] Varchar(255), [Amount] Decimal(19,4), [% VAT] Decimal(5,4), [VAT Amount] Decimal(19,4),[Date Cleared] Date, Memo varchar(max))

Insert into @Trans (ID,[Date],[Description],[Amount],[Date Cleared]) Values(0,@OpeningDate, 'Opening Balance',@OpeningBalance,@OpeningDate)

Insert into @Trans
Select T.ID, T.[Reference], T.[Cheque Number],T.Date, T.Description,
Case
When Count(C.ID) > 1 Then '[Split Categories]'
Else Max(C.[Category Name])
End as [Category Name],
Sum(D.Value) as [Amount],
Case
When Sum(D.[VAT Rate]) = 0 Then Null
Else Sum(D.[VAT Rate])
End as [% VAT],
Case
When Sum(D.[VAT Value]) = 0 Then Null
Else Sum(D.[VAT Value])
End as [VAT Amount],

T.[Date Cleared],
T.[Memo]
  
From Transactions T INNER JOIN Transactions_Detail D on T.ID = D.TransactionID
					INNER JOIN Categories C on D.Category = C.ID
Where Account = @Account
Group By T.ID, T.Date, T.Description, T.[Date Cleared], T.[Cheque Number], T.[Reference], T.[Memo]
Order By Date 

Declare @Balance Table (ID int, Balance Decimal(19,4))
DECLARE @ID Int,
		@Amount Decimal(19,2),
		@RunningTotal money

SET @RunningTotal = 0

DECLARE rt_cursor CURSOR
FOR
SELECT ID, Amount FROM @Trans Order by Date

OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO @ID,@Amount

WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @RunningTotal = @RunningTotal + @Amount
  INSERT @Balance VALUES (@ID,@RunningTotal)
  FETCH NEXT FROM rt_cursor INTO @ID,@Amount
 END

CLOSE rt_cursor
DEALLOCATE rt_cursor

If @VATEnabled = 1 
Begin
	Select T.*, B.Balance From @Trans T Inner Join @Balance B on T.ID = B.ID Order by Date
End Else Begin
	Select T.ID, T.Reference, T.[Cheque Number], T.[Date], T.Description, T.[Category Name], T.Amount, T.[Date Cleared], T.Memo, B.Balance From @Trans T Inner Join @Balance B on T.ID = B.ID Order by Date
End