Customer Ledger

#1

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
0 Likes

#2

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

0 Likes

#3

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

0 Likes

#4

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.

0 Likes

#5

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
0 Likes

#6

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.

0 Likes

#7

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

#8

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.

0 Likes

#9

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

0 Likes