April 11, 2019 at 5:33 pm
I know I can solve this problem using a looping strategy but I was hoping someone could suggest a way to do this without a row over row solution.
There are 2 tables - one containing invoices and one containing possible credits.
If I was doing a looping solution I would take the largest credit and start applying it to the invoices in order of largest to smallest until the balance of the credit is zero, then I would move on to the next to the next credit until I had no credits and no invoices left.
In the example below the first 2 credits should be fully used. The third credit should should be partially used and the last credit should go untouched
Any advice?
The structure and the data is listed below...
drop table #InvoicesWithBalances
drop table #AvailableCredits
create table #InvoicesWithBalances
(
InvoiceKey decimal(18,0) not null,
APBalance decimal(18,6) null,
BalanceAfterCreditApplied decimal(18,6) null,
ShipToNumber nvarchar(10),
SupplierNumber nvarchar(10)
)
create table #AvailableCredits
(
InvoiceKey decimal(18,0) not null,
StartingBalance decimal(18,6) null,
CurrentBalance decimal(18,6) null,
ShipToNumber nvarchar(10),
SupplierNumber nvarchar(10)
)
insert into #InvoicesWithBalances (invoicekey,APBalance,BalanceAfterCreditApplied,ShipToNumber,SupplierNumber)
select distinct documentkey,balance,balance,ShipToNumber,SupplierNumber from EFT_AR_AP_Snapshot a inner join invoice b on a.documentkey = b.invoicekey
where startdate < CURRENT_TIMESTAMP and enddate = '2050-01-01' and EntryType = 1 and balance > 0
and TermsDiscountDueDate <= CURRENT_TIMESTAMP
insert into #AvailableCredits (invoicekey,StartingBalance,CurrentBalance,ShipToNumber,SupplierNumber)
select distinct documentkey,balance,balance,ShipToNumber,SupplierNumber from EFT_AR_AP_Snapshot a inner join invoice b on a.documentkey = b.invoicekey
where startdate < CURRENT_TIMESTAMP and enddate = '2050-01-01' and EntryType = 1 and balance < 0
and TermsDiscountDueDate <= CURRENT_TIMESTAMP
insert into #InvoicesWithBalances values (5452, 13744.080000, 13744.080000, 'C100101', 'ACCU17')
insert into #InvoicesWithBalances values (7056, 13744.080000, 13744.080000, 'C100101', 'ACCU17')
insert into #InvoicesWithBalances values (7438, 500.000000, 500.000000 , 'C100101', 'ACCU17')
insert into #AvailableCredits values (9580, -13744.080000, -13744.080000, 'C100101', 'ACCU17')
insert into #AvailableCredits values (11135, -13700.080000, -13700.080000, 'C100101', 'ACCU17')
insert into #AvailableCredits values (11136, -500.000000, -500.000000, 'C100101', 'ACCU17')
insert into #AvailableCredits values (9581, -500.000000, -500.000000, 'C100101', 'ACCU17')
select * from #InvoicesWithBalances where suppliernumber = 'ACCU17' and shiptonumber = 'C100101' order by apbalance desc
select * from #AvailableCredits where suppliernumber = 'ACCU17' and shiptonumber = 'C100101' order by startingbalance asc
April 12, 2019 at 6:14 pm
While there is probably a way to calculate all invoices and credits in a single query using recursive CTEs, I think this case is better served by using loops. In my opinion, you are dealing with individual financial transactions, and each transaction should be completed and committed before moving on to other transactions. I would also have a table for the applied values, and record the invoice, the credit, the amount applied, and the before-and-after values for both the invoice and credit. The transaction would create the applied record, update the invoice, update the credit, error check, then commit. Once that is finished and "in the books", I would move on to the next set. So I think your first instincts are correct.
By the way, you may want to start with the oldest credit first, rather than starting with the largest. That minimizes the possibility that an older, smaller check remains unprocessed and is eventually canceled by the bank before it gets used. I also suggest applying to the oldest invoice first, minimizing the possibility of violating customers' terms. If there is a older, small invoice at "net 30", but the newer invoices are larger so get first priority, it may be more than 30 days before the invoice gets credited, thus incurring penalties. Those are my suggestions, not knowing your specific business, so discard what doesn't make sense for your case.
April 12, 2019 at 10:41 pm
Thanks Jonathan
Your points about the additional data in the tables are well taken - this is just a simplified example for the purpose of this post.
I was convinced going into this that using loops was the only way to do it and I am glad that at least one person agreed with me....
Thanks for taking the time to respond...
TF
April 16, 2019 at 3:16 pm
If anyone is interested... here is what we came up with...
drop table #InvoicesWithBalances
drop table #AvailableCredits
drop table #invoice
drop table #credits
create table #InvoicesWithBalances
(
InvoiceKey decimal(18,0) not null,
Balance decimal(18,6) null,
ShipToNumber nvarchar(10),
SupplierNumber nvarchar(10)
)
create table #AvailableCredits
(
InvoiceKey decimal(18,0) not null,
Balance decimal(18,6) null,
ShipToNumber nvarchar(10),
SupplierNumber nvarchar(10)
)
--- create rownumber based on ShipToNumber,SupplierNumber
create table #invoice
(
invoice_row_num int,
InvoiceKey int,
ShipToNumber nvarchar(10),
SupplierNumber nvarchar(10),
Balance decimal (18,6),
RunningTotal decimal (18,6)
);
create table #credits
(
credit_row_num int,
InvoiceKey int,
ShipToNumber nvarchar(10),
SupplierNumber nvarchar(10),
Balance decimal (18,6),
RunningTotal decimal (18,6),
oktouse bit
);
-- test data
insert into #InvoicesWithBalances values (5452, 13744.080000, 'C100101', 'ACCU17')
insert into #InvoicesWithBalances values (7056, 13744.080000, 'C100101', 'ACCU17')
insert into #InvoicesWithBalances values (7438, 500.000000, 'C100101', 'ACCU17')
insert into #AvailableCredits values (9580, -13744.080000, 'C100101', 'ACCU17')
insert into #AvailableCredits values (11135, -13700.080000, 'C100101', 'ACCU17')
insert into #AvailableCredits values (11136, -500.000000, 'C100101', 'ACCU17')
insert into #AvailableCredits values (9581, -500.000000, 'C100101', 'ACCU17')
-- move the data to the invoice table and assign a row number... order by biggest invoices to smallest
insert into #invoice
select ROW_NUMBER() OVER (PARTITION BY ShipToNumber,SupplierNumber ORDER BY Balance desc) as row_num, InvoiceKey, ShipToNumber,SupplierNumber,balance,0 FROM #InvoicesWithBalances;
-- move data to the credit table and give it a row number - order by biggest credit to smallest
insert into #credits
select ROW_NUMBER() OVER (PARTITION BY ShipToNumber,SupplierNumber ORDER BY Balance asc) as row_num,InvoiceKey, ShipToNumber,SupplierNumber,balance,0,0 FROM #AvailableCredits;
-- calculate the total for each member/supplier pair as a running total for each line
;WITH CTE
AS ( SELECT invoice_row_num,InvoiceKey , Balance ,ShipToNumber ,SupplierNumber ,
RunningTotal = Balance
FROM #invoice
WHERE invoice_row_num IN ( SELECT MIN(invoice_row_num)
FROM #invoice
GROUP BY ShipToNumber ,SupplierNumber )
UNION ALL
SELECT y.invoice_row_num ,y.InvoiceKey , y.Balance , y.ShipToNumber ,y.SupplierNumber ,
RunningTotal = cast(x.RunningTotal + y.Balance as decimal(18,6))
FROM CTE x
JOIN #invoice y ON y.ShipToNumber = x.ShipToNumber and y.SupplierNumber = x.SupplierNumber
AND y.invoice_row_num = x.invoice_row_num + 1
)
update i
set i.RunningTotal = c.RunningTotal
FROM #invoice i
inner join CTE c on i.ShipToNumber = c.ShipToNumber and i.SupplierNumber = c.SupplierNumber and i.InvoiceKey = c.InvoiceKey and i.invoice_row_num = c.invoice_row_num
-- now calcualte the running total for the credits
;WITH CTE
AS ( SELECT credit_row_num,InvoiceKey , Balance ,ShipToNumber ,SupplierNumber ,
RunningTotal = Balance
FROM #credits
WHERE credit_row_num IN ( SELECT MIN(invoice_row_num)
FROM #invoice
GROUP BY ShipToNumber ,SupplierNumber )
UNION ALL
SELECT y.credit_row_num ,y.InvoiceKey , y.Balance , y.ShipToNumber ,y.SupplierNumber ,
RunningTotal = cast(x.RunningTotal + y.Balance as decimal (18,6))
FROM CTE x
JOIN #credits y ON y.ShipToNumber = x.ShipToNumber and y.SupplierNumber = x.SupplierNumber
AND y.credit_row_num = x.credit_row_num + 1
)
update i
set i.RunningTotal = c.RunningTotal
FROM #credits i
inner join CTE c on i.ShipToNumber = c.ShipToNumber and i.SupplierNumber = c.SupplierNumber and i.InvoiceKey = c.InvoiceKey and i.credit_row_num = c.credit_row_num
-- now we need to find the credits we can use. If the sum total of the credits in the running total + plust the invoices is greater than 0 , then we can use the credit.
update cc set cc.oktouse = 1
from #credits cc
inner join
(
select max(credit_row_num) as max_credit_row_num ,c.ShipToNumber,c.SupplierNumber from #invoice i
inner join #credits c on i.ShipToNumber = c.ShipToNumber and i.SupplierNumber = c.SupplierNumber and i.RunningTotal +c.Runningtotal > 0
group by c.ShipToNumber,c.SupplierNumber
) x
on x.ShipToNumber = cc.ShipToNumber and x.SupplierNumber = cc.SupplierNumber and x.max_credit_row_num> = cc.credit_row_num
select * from #Invoice
select * from #credits
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply