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