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