download SQL Server 2016
download SQL Server 2014
SQL Tutorial - How to Map Payments to Expenses using T-SQL Query
In your SQL Server database you have two database tables to store expenses and payments for closing expense amount.
You want to list the expenses of a customer using t-sql by querying your sql tables.
This list will also add the payments done by the same customer to close these expenses.
Each payment will be listed as new columns beside the expenses records.
Here is sql script for creating sample data for our sql problem.
The tsql script contains CREATE TABLE statements which creates sql tables in database to store expenses and payments data.
--DROP TABLE PrePayments
--DROP TABLE Expenses
CREATE TABLE PrePayments (
CREATE TABLE Expenses (
INSERT INTO PrePayments SELECT 1,1,100,'20100101'
INSERT INTO PrePayments SELECT 2,1,200,'20100201'
INSERT INTO Expenses SELECT 1,1,30,'20100301'
INSERT INTO Expenses SELECT 2,1,20,'20100302'
INSERT INTO Expenses SELECT 3,1,30,'20100303'
INSERT INTO Expenses SELECT 4,1,30,'20100304'
INSERT INTO Expenses SELECT 5,1,20,'20100305'
INSERT INTO Expenses SELECT 6,1,40,'20100306'
INSERT INTO Expenses SELECT 7,1,20,'20100307'
INSERT INTO Expenses SELECT 8,1,30,'20100308'
INSERT INTO Expenses SELECT 9,1,20,'20100309'
INSERT INTO Expenses SELECT 10,1,20,'20100310'
INSERT INTO Expenses SELECT 11,1,20,'20100311'
INSERT INTO Expenses SELECT 12,1,40,'20100312'
The below t-sql script maps each payment to an expense entry.
The payment to expense mapping is managed by t-sql select statement in an order according to Payment date and Expense date.
If partial payments are done, the below t-sql script handles this situation and decreases the amount to be paid by the payment amount.
And the following payment in date order is used to close the current expense amount.
If the payment amount is more than the expense amount, this extra payment amount is used for the payment of the next expense.
The following payment to expenses sql script will handle this situation also.
with cte as (
total_payments = SUM(pp.PaymentAmount),
from PrePayments p
inner join PrePayments pp on p.PayerId = pp.PayerId and p.PaymentDate >= pp.PaymentDate
where p.PayerId = 1
y.ExpensedById, y.ExpenseId, y.ExpenseDate, y.ExpenseAmount,
cte.PaymentId, cte.PaymentAmount, cte.PaymentDate PrePayments_date,
total_difference = (total_payments - total_expenses)
PaymentId = (select MIN(PaymentId) from cte where total_expenses <= total_payments)
total_expenses = sum(ee.ExpenseAmount),
from Expenses e
inner join Expenses ee on e.ExpensedById = ee.ExpensedById and e.ExpenseDate >= ee.ExpenseDate
where e.ExpensedById = 1
left join cte on cte.PaymentId = y.PaymentId
The output of the above t-sql CTE query will result as follows :
Related SQL Resources
SQL Server Articles
SQL Server 2012
SQL Server Tools
MS SQL Server Forums