T-SQL - Adjusting Payments Against Invoices and Calculate Balances
-
Hi all,
I have been working on the following task where balances of payments exist in one table and invoices in other table for different customers. Requirement is to settle total payments against invoices in the following way:
Payment table have following information of payment:
Cust_ID, Balance_Payment
1, 500
2, 800
3, 50Invoices table contain following data of invoices:
Cust_ID, Invoice_Order, Invoice_Date, Invoice_Amount
1, 1, 11/25/2016, 100
1, 2, 11/26/2016, 200
1, 3, 11/27/2016, 300
2, 1, 11/25/2016, 100
2, 2, 11/26/2016, 200
2, 3, 11/27/2016, 300
3, 1, 11/25/2016, 100
3, 2, 11/26/2016, 200
3, 3, 11/27/2016, 300Following result is required:
Cust_ID, Invoice_Order, Invoice_Date, Invoice_Amount, Adjusted_Payment, Remaining_Payment, Invoice_Balance
1, 1, 11/25/2016, 100, 100, 400, -
1, 2, 11/26/2016, 200, 200, 200, -
1, 3, 11/27/2016, 300, 200, -, 100
2, 1, 11/25/2016, 100, 100, 700, -
2, 2, 11/26/2016, 200, 200, 500, -
2, 3, 11/27/2016, 300, 300, 200, -
3, 1, 11/25/2016, 100, 50, 50
3, 2, 11/26/2016, 200, - , - , 200
3, 3, 11/27/2016, 300, - , - , 300Excel work book attached for same data.
Any idea how could it could be done in preferably SQL 2000 or SQL 2005/08 - drew.allen - Wednesday, January 25, 2017 8:37 AM
Yes, my first point was going to be that it would be much easier if he could do it on 2012 or later. I was also going to suggest that he provide CREATE TABLE and INSERT statements, to make it much likelier that someone will go to the trouble to write a query for him.
John
- John Mitchell-245523 - Wednesday, January 25, 2017 8:41 AM
Thanks John for the referred article , i will try it. I am also reading your post regarding "How to Post Data/Code etc".
- Rehan Ahmad - Wednesday, January 25, 2017 9:15 AM
Much as I'd like to take the credit, it was Drew who posted the link, and Drew in whose signature the reference to the posting etiquette appears!
John
- John Mitchell-245523 - Wednesday, January 25, 2017 9:21 AM
I also updated the link in my post that John quoted, so you should use that link instead.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Dears,
following are the queries to create required tables:-------- CREATE TABLE [TABLE_BALANCE] ------------
CREATE TABLE [dbo].[TABLE_BALANCE](
[STORE_ID] [varchar](10) NULL,
[RECEIPT_NO] [varchar](10) NULL,
[RECEIVABLE] [numeric](38, 0) NULL,
[PAYMENTS] [numeric](38, 0) NULL,
[BALANCE] [numeric](38, 0) NULL
) ON [PRIMARY]GO
INSERT INTO [dbo].[TABLE_BALANCE]
(STORE_ID,RECEIPT_NO,RECEIVABLE,PAYMENTS,BALANCE)
VALUES
('ABC123','8211222992',663.91,0,0),
('ABC123','8245001700',70.84,0,0),
('DFE003','8047814762',137.67,0,0),
('DFE003','8200789513',54.93,0,0),
('HIJ909','8227682393',34.05,0,0),
('HIJ909','8231077673',32.96,0,0),
('FR222','8219374733',179.56,0,0),
('FR222','8219478281',32.96,0,0),
('FR222','8220000191',1118.48,0,0)
GO------- CREATE TABLE [TABLE_PAYMENTS] ----------
CREATE TABLE [dbo].[TABLE_PAYMENTS](
[STORE_ID] [varchar](10) NULL,
[PAYMENTS] [numeric](38, 0) NULL)
GOINSERT INTO [dbo].[TABLE_PAYMENTS]
(STORE_ID,PAYMENTS)
VALUES
('ABC123',300),
('DFE003',515.5),
('FR222',1000),
('HIJ909',50)
-
And the expected results based on that, please?
Edit: oops, ignore that! Didn't see your update to your post before I posted.
Thanks
John -
Three questions:
(1) Are payments to be applied in order of receipt number, so that the lowest-numbered receipt for any store gets paid first?
(2) In your required output, should the value for PAYMENTS for RECEIPT_NO 8200789513 not be 54.93?
(3) Why do your tables have a data type for sums of money that doesn't support decimal places?John
-
(1) Are payments to be applied in order of receipt number, so that the lowest-numbered receipt for any store gets paid first?
-- No sorting order required on receipt, first row will be adjusted first, rowid might work on this
(2) In your required output, should the value for PAYMENTS for RECEIPT_NO 8200789513 not be 54.93?
-- Typo mistake, as payment availble is greate than 54.93, the whole amount 54.93 will be shown in payment and balance will be Zero
(3) Why do your tables have a data type for sums of money that doesn't support decimal places?
-- Dear, please suggest the data type while importing the values from txt file. I will change itScenario is simple, that we have total payment amount made by a customer/store, and now we have to adjust these payments against unpaid receipts for same customer. The receipts might by fully paid, partially paid or unpaid after adjusting payments.
- Rehan Ahmad - Thursday, February 2, 2017 5:50 AM
-- No sorting order required on receipt, first row will be adjusted first, rowid might work on this
You need to sort on something. You can't just say the first row, because rows don't have an inherent order in a table.
-- Typo mistake, as payment availble is greate than 54.93, the whole amount 54.93 will be shown in payment and balance will be Zero
Good. Please repost your required output.
-- Dear, please suggest the data type while importing the values from txt file. I will change it
Have you not already imported the data from tables, and did you not notice that you were missing the pence or cents or whatever? See here for how to choose your data type.John
-
Dear, Sorting on Receipt_No but Store_ID wise. Updated script is as below:
-------- CREATE TABLE [TABLE_BALANCE] --------------DROP TABLE [TABLE_BALANCE]
CREATE TABLE [dbo].[TABLE_BALANCE](
[STORE_ID] [varchar](10) NULL,
[RECEIPT_NO] [varchar](10) NULL,
[RECEIVABLE] [decimal](38,2) NULL,
[PAYMENTS] [decimal](38,2) NULL,
[BALANCE] [decimal](38,2) NULL
) ON [PRIMARY]GO
INSERT INTO [dbo].[TABLE_BALANCE]
(STORE_ID,RECEIPT_NO,RECEIVABLE,PAYMENTS,BALANCE)
VALUES
('ABC123','8211222992',663.91,0,0),
('ABC123','8245001700',70.84,0,0),
('DFE003','8047814762',137.67,0,0),
('DFE003','8200789513',54.93,0,0),
('HIJ909','8227682393',34.05,0,0),
('HIJ909','8231077673',32.96,0,0),
('FR222','8219374733',179.56,0,0),
('FR222','8219478281',32.96,0,0),
('FR222','8220000191',1118.48,0,0)
GO------- CREATE TABLE [TABLE_PAYMENTS] ----------
--DROP TABLE [TABLE_PAYMENTS]CREATE TABLE [dbo].[TABLE_PAYMENTS](
[STORE_ID] [varchar](10) NULL,
[PAYMENTS] [decimal](38,2) NULL,
[BALANCE] [decimal](38,2) NULL)
GOINSERT INTO [dbo].[TABLE_PAYMENTS]
(STORE_ID,PAYMENTS,BALANCE)
VALUES
('ABC123',300,0),
('DFE003',515.5,0),
('FR222',1000,0),
('HIJ909',50,0)SELECT * FROM dbo.TABLE_PAYMENTS
SELECT * FROM dbo.TABLE_BALANCEExcel file also attached.
-
Here you go. I don't know how well it'll perform when you release it into the wild. The query would be a lot simpler and perform better if you could use the windowing functions available in 2012 and later. Please make sure you understand what's going on before you use in production - you'll be the one who's supporting it, after all.
WITH BalanceAndPayments AS ( -- Join tables to get balances and payments all in one place
SELECT
b.STORE_ID
, b.RECEIPT_NO
, b.RECEIVABLE
, p.PAYMENTS
, ROW_NUMBER() OVER (PARTITION BY b.STORE_ID ORDER BY b.RECEIPT_NO) AS RowNo
FROM TABLE_BALANCE b
LEFT JOIN TABLE_PAYMENTS p ON b.STORE_ID = p.STORE_ID -- left join in case any stores with no payments
)
, RunningTotals AS ( -- self-join to get running total of receivables
SELECT DISTINCT
b1.STORE_ID
, b1.RECEIPT_NO
, b1.RECEIVABLE
, SUM(b2.RECEIVABLE) OVER (PARTITION BY b1.STORE_ID, b1.RECEIPT_NO) AS SubTotal
, b1.PAYMENTS
, b1.RowNo
FROM BalanceAndPayments b1
JOIN BalanceAndPayments b2
ON b2.RECEIPT_NO <= b1.RECEIPT_NO AND b1.STORE_ID = b2.STORE_ID
)
, Comparisons AS ( -- self-join to compare each row to the one before it
SELECT
r1.STORE_ID
, r1.RECEIPT_NO
, r1.RECEIVABLE
, r1.SubTotal
, r1.PAYMENTS
, COALESCE(r2.SubTotal,0) AS PreviousSubTotal
FROM RunningTotals r1
LEFT JOIN RunningTotals r2 ON r1.STORE_ID = r2.STORE_ID AND r1.RowNo = r2.RowNo + 1
)
SELECT
STORE_ID
, RECEIPT_NO
, RECEIVABLE
, CASE
WHEN PAYMENTS >= SubTotal THEN RECEIVABLE -- payment larger than total of all receivables
WHEN PAYMENTS - PreviousSubTotal >= RECEIVABLE THEN RECEIVABLE -- enough left from payment to pay this receivable in full
WHEN PAYMENTS < PreviousSubTotal THEN 0 -- not enough left from payment to cover any of this receivable
WHEN PAYMENTS - PreviousSubTotal < RECEIVABLE THEN PAYMENTS - PreviousSubTotal -- only enough left from payment to pay part of this receivable
END AS PaymentAssigned
, CASE
WHEN PAYMENTS >= SubTotal THEN 0 -- payment larger than total of all receivables
WHEN PAYMENTS - PreviousSubTotal >= RECEIVABLE THEN 0 -- only left from payment to pay his receivable in full
WHEN PAYMENTS < PreviousSubTotal THEN SubTotal - PreviousSubTotal -- not enough left from payment to cover any of this receivable
WHEN PAYMENTS - PreviousSubTotal < RECEIVABLE THEN RECEIVABLE - PAYMENTS + PreviousSubTotal -- only enough left from payment to pay part of this receivable
END AS ReceiptBalance
FROM Comparisons
ORDER BY
STORE_ID
, RECEIPT_NOJohn
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply