Customer Ledger

I have modified the query in the above tutorial is to show statements (Ledger) for every customer, regardless of whether or not they have an account.

I am stuck with bringing the balance forward. I think this section is where the issue is.

  -- loop filtered records to get Balance Brought Forward previous to @dateFilterBeg
SET @balance = 0.00
SET @i = 0

WHILE @i<=@txcount
BEGIN
IF @dateFilterBeg > (SELECT [Date] FROM @tbl_ledger WHERE [Id]=@i)
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_ledger WHERE [Id]=@i)
UPDATE @tbl_ledger SET [Balance] = @balance WHERE [Id]=@i
END
SET @i = @i + 1
END


-- insert Balance Brought Forward
IF @dateFilterBeg > (SELECT MIN([Date]) FROM @tbl_ledger)
BEGIN
INSERT INTO @tbl_ledgercomplete ([Date], [Description], [Credit], [Debit], [Balance])
SELECT @dateFilterBeg, 'Balance Brought Forward', null, null,@balance
END

The problem is it sets the @balance to the first balance value and not the Balance at the specified date.

Here is my query for reference. Its a bit all over the place as I have been going back and adding things as they were required. Once it working I will remove all the unnecessary actions.

declare @entityId int = 0
declare @dateFilterBeg datetime = GETDATE()

set		@dateFilterBeg  = '2017-01-16'
set		@entityId  = '1255'

declare @txcount int = 0
declare @i int = 1
declare @balance decimal(16,2) = 0.00
declare @lastZeroDate varchar(50) = '2000-01-01'

--IF @dateFilterBeg = ''
 --BEGIN
 -- SET @dateFilterBeg = '2000-01-01'
--END

declare		@tbl_tickets table (
			[Date] Datetime null,
			[InvoiceId] varchar(255) null,
			[Debit] decimal(16,2) null,
			[Credit] decimal(16,2) null
			)

INSERT INTO	@tbl_tickets ([Date], [InvoiceId], [Debit], [Credit])

Select		Date as [Date],
			Tickets.Id as [InvoiceId],
			TotalAmount as [Debit],
			'0.00' as [Credit]
FROM		Tickets
INNER JOIN	TicketEntities
		ON	Tickets.Id = TicketEntities.Ticket_Id
WHERE		EntityId = @entityid and TicketTypeId != '4'

declare		@tbl_ticketpayments table (
			[Date] Datetime null,
			[PmtName] varchar(255) null,
			[Debit] decimal(16,2) null,
			[Credit] decimal(16,2) null
			)

INSERT INTO	@tbl_ticketpayments ([Date], [PmtName], [Debit], [Credit])

Select		Payments.Date as [Date],
			Payments.Name as [PmtName],
			'0.00' as [Debit],
			Amount as [Credit]
FROM		Payments
INNER JOIN	Tickets 
		ON	Payments.TicketId = Tickets.Id 
INNER JOIN	TicketTypes 
		ON	Tickets.TicketTypeId = TicketTypes.Id
INNER JOIN	TicketEntities
		ON	Tickets.Id = TicketEntities.Ticket_Id
WHERE		TicketTypes.Name !='Quote' and 
			EntityId = @entityId and 
			(Payments.Name = 'Cash' or Payments.Name = 'Credit Card' or Payments.Name = 'Banking Online' or Payments.Name = 'Cheque')

declare		@tbl_accountpayments table (
			[Date] Datetime null,
			[PmtName] varchar(255) null,
			[Debit] decimal(16,2) null,
			[Credit] decimal(16,2) null
			)

INSERT INTO @tbl_accountpayments ([Date], [PmtName], [Debit], [Credit])

SELECT		AccountTransactionValues.Date as [Date],
			AccountTransactions.Name as [PmtName],
			'0.00' AS [Debit], 
			Amount AS [Credit]
FROM		AccountTransactions
INNER JOIN	AccountTransactionDocuments
		ON	AccountTransactions.AccountTransactionDocumentId = AccountTransactionDocuments.Id
INNER JOIN	AccountTransactionValues
		ON	AccountTransactions.Id= AccountTransactionValues.AccountTransactionId
INNER JOIN	Entities
		ON	AccountTransactionValues.AccountId = Entities.AccountId
WHERE		(AccountTransactions.Name LIKE '%Cash Payment%' or AccountTransactions.Name LIKE '%Banking Online%' or AccountTransactions.Name LIKE '%Cheque Payment%' or AccountTransactions.Name LIKE '%Credit Card%') and 
			AccountTransactions.Name NOT LIKE '%Transaction%' and 
			Entities.Id = @entityId

declare		@tbl_ledgersort table (
			[Id]   INT IDENTITY(1,1) NOT NULL,
			[Date] Datetime null,
			[Description] varchar(255) null,
			[Debit] decimal(16,2) null,
			[Credit] decimal(16,2) null,
			[Amount] decimal(16,2) null,
			[Balance] decimal(16,2) null
			)

INSERT INTO @tbl_ledgersort ([Date], [Description], [Debit], [Credit], [Amount])

SELECT		Date as [Date],
			'Invoice # ' + InvoiceId as [Description],
			Debit as [Debit],
			'0.00' as [Credit],
			Debit - Credit as [Amount]
FROM		@tbl_tickets

INSERT INTO @tbl_ledgersort ([Date], [Description], [Debit], [Credit], [Amount])

SELECT		Date as [Date],
			PmtName + ' Payment' as [Description],
			'0.00' as [Debit],
			Credit as [Credit],
			Debit - Credit as [Amount]
FROM		@tbl_ticketpayments
			
INSERT INTO @tbl_ledgersort ([Date], [Description], [Debit], [Credit], [Amount])

SELECT		Date as [Date],
			PmtName as [Description],
			'0.00' as [Debit],
			Credit as [Credit],
			Debit - Credit as [Amount]
FROM		@tbl_accountpayments

declare		@tbl_ledger table (
			[Id]   INT IDENTITY(1,1) NOT NULL,
			[Date] Datetime null,
			[Description] varchar(255) null,
			[Debit] decimal(16,2) null,
			[Credit] decimal(16,2) null,
			[Amount] decimal(16,2) null,
			[Balance] decimal(16,2) null
			)

INSERT INTO @tbl_ledger ([Date], [Description], [Debit], [Credit], [Amount], [Balance])
SELECT      Date as [Date],
			Description as [Description],
			Debit as [Debit],
			Credit as [Credit],
			Amount as [Amount],
			Balance as [Balance]
FROM		@tbl_ledgersort
ORDER BY	Date


declare		@tbl_ledgercomplete table (
			[Id]   INT IDENTITY(1,1) NOT NULL,
			[Date] Datetime null,
			[Description] varchar(255) null,
			[Debit] decimal(16,2) null,
			[Credit] decimal(16,2) null,
			[Balance] decimal(16,2) null
			)

-- get count of records
SELECT @txcount = count([Id]) FROM @tbl_ledger


-- loop all records to set Last 0 Balance Date
WHILE @i<=@txcount
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_ledger WHERE [Id]=@i)
UPDATE @tbl_ledger SET [Balance] = @balance WHERE [Id]=@i
--IF @balance = 0 UPDATE [ProgramSettingValues] SET [Value]=(SELECT [TxDate] FROM @tbl_tx_filtered WHERE [Id]=@i) WHERE [Name]='AS Entity Date_ZeroBalance'
IF @balance = 0 SET @lastZeroDate = replace((SELECT convert(varchar(50),[Date],126) FROM @tbl_ledger WHERE [Id]=@i),'T',' ')
SET @i = @i + 1
END

UPDATE [ProgramSettingValues] SET [Value]=@lastZeroDate WHERE [Name]='AS Entity Date_ZeroBalance'
--SELECT isnull(max([TxDate]),'2000-01-01') FROM @tbl_tx_all WHERE [Balance] = 0.00
--print '------------------------------------------- ' + @lastZeroDate

-- loop filtered records to get Balance Brought Forward previous to @dateFilterBeg
SET @balance = 0.00
SET @i = 0

WHILE @i<=@txcount
BEGIN
IF @dateFilterBeg > (SELECT [Date] FROM @tbl_ledger WHERE [Id]=@i)
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_ledger WHERE [Id]=@i)
UPDATE @tbl_ledger SET [Balance] = @balance WHERE [Id]=@i
END
SET @i = @i + 1
END


-- insert Balance Brought Forward
IF @dateFilterBeg > (SELECT MIN([Date]) FROM @tbl_ledger)
BEGIN
INSERT INTO @tbl_ledgercomplete ([Date], [Description], [Credit], [Debit], [Balance])
SELECT @dateFilterBeg, 'Balance Brought Forward', null, null,@balance
END

-- insert Filtered Transactions
INSERT INTO @tbl_ledgercomplete ([Date], [Description], [Credit], [Debit], [Balance])
SELECT [Date], [Description], [Credit], [Debit], [Balance] FROM @tbl_ledger WHERE [Date] >= @dateFilterBeg


-- loop records to use filtered amounts and Balance Brought Forward
SELECT @txcount = count([Id]) FROM @tbl_ledgercomplete
SET @i=1
SET @balance = 0.00

WHILE @i<=@txcount
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_ledger WHERE [Id]=@i)
UPDATE @tbl_ledgercomplete SET [Balance] = @balance WHERE [Id]=@i
SET @i = @i + 1
END


-- SET @balance
-- UPDATE [ProgramSettingValues] SET [Value]=@lastZeroDate WHERE [Name]='AS Entity Date_ZeroBalance'
Select		*
FROM		@tbl_ledger
UNION ALL
SELECT      '',CURRENT_TIMESTAMP,'TOTALS',SUM(Debit),SUM(Credit),SUM(Debit)-SUM(Credit),SUM(Debit)-SUM(Credit)
From		@tbl_ledger;

Select		*
FROM		@tbl_ledgercomplete

Please show an example screenshot of that so I can understand better what you mean.

It seems to be just inserting the balance from the top line down rather than from the filtered date.

Top section is all results

Bottom is Filtered from 2017-01-20 Balance brought forward should be 459.99 not 270.00

Do you see any Messages in SSMS regarding warnings?

I wonder about this:

-- insert Balance Brought Forward
IF @dateFilterBeg > (SELECT MIN([Date]) FROM @tbl_ledger)
BEGIN
INSERT INTO @tbl_ledgercomplete ([Date], [Description], [Credit], [Debit], [Balance])
SELECT @dateFilterBeg, 'Balance Brought Forward', null, null,@balance
END

Specifically the null values, because you shouldn’t do aggregates (ie. SUM()) on a column when some values are NULL, which you do later in the script.:

SELECT @dateFilterBeg, 'Balance Brought Forward', null, null,@balance
                                                  ^^^^  ^^^^

It probably is not causing your issue, but try changing those nulls to 0 instead.

Oh I see something that is not right - don’t know how this would affect things, but this is not correct:

-- loop filtered records to get Balance Brought Forward previous to @dateFilterBeg
SET @balance = 0.00
SET @i = 0

That @i var should be set to 1, not 0 … that is my bad…

-- loop filtered records to get Balance Brought Forward previous to @dateFilterBeg
SET @balance = 0.00
SET @i = 1

No I was getting no errors. I had planned on putting a case there to specify which column the @balance should be inserted into. I have set both to @balance and the balance is correct but the same figure in the balance column just shows as 0.00.

Here is a different entity where the first balance figure is not 0. Again @ balance in Debit-Credit columns is correct but in the balance column it just seems to be taking the first balance figure from the top table.

Could it be because you are sorting by Date in the opposite direction?

Have you tried using ORDER BY Date DESC ?

INSERT INTO @tbl_ledger ([Date], [Description], [Debit], [Credit], [Amount], [Balance])
SELECT      Date as [Date],
			Description as [Description],
			Debit as [Debit],
			Credit as [Credit],
			Amount as [Amount],
			Balance as [Balance]
FROM		@tbl_ledgersort
ORDER BY	Date DESC

You should still be able to sort using ORDER BY [Date] DESC in your last SELECT statement to reverse the order.

Select		*
FROM		@tbl_ledger
ORDER BY [Date] DESC
1 Like

Removing this

SET @i=1
SET @balance = 0.00

from here

-- loop records to use filtered amounts and Balance Brought Forward
SELECT @txcount = count([Id]) FROM @tbl_tx_filtered
SET @i=1
SET @balance = 0.00

seems to have solved it. It was resetting back to before the date filter.

Hi Silent Bob. I have a requirement like this…Could you please share your code on how you got to your final ledger.
Thanks