SQL Tutorial - How to Map Payments to Expenses using T-SQL Query
Problem Statement:
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 (
PaymentId int,
PayerId int,
PaymentAmount int,
PaymentDate datetime
);
GO
CREATE TABLE Expenses (
ExpenseId int,
ExpensedById int,
ExpenseAmount int,
ExpenseDate datetime
);
GO
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'
GO
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 (
select
p.PaymentId,
p.PaymentAmount,
total_payments = SUM(pp.PaymentAmount),
p.PaymentDate
from PrePayments p
inner join PrePayments pp on p.PayerId = pp.PayerId and p.PaymentDate >= pp.PaymentDate
where p.PayerId = 1
group by
p.PaymentId,
p.PaymentAmount,
p.PaymentDate
)
select
y.ExpensedById, y.ExpenseId, y.ExpenseDate, y.ExpenseAmount,
cte.PaymentId, cte.PaymentAmount, cte.PaymentDate PrePayments_date,
total_expenses,
total_payments,
total_difference = (total_payments - total_expenses)
from (
select
*,
PaymentId = (select MIN(PaymentId) from cte where total_expenses <= total_payments)
from (
select
e.ExpensedById,
e.ExpenseId,
e.ExpenseAmount,
total_expenses = sum(ee.ExpenseAmount),
e.ExpenseDate ExpenseDate
from Expenses e
inner join Expenses ee on e.ExpensedById = ee.ExpensedById and e.ExpenseDate >= ee.ExpenseDate
where e.ExpensedById = 1
group by
e.ExpensedById,
e.ExpenseId,
e.ExpenseAmount,
e.ExpenseDate
) x
) y
left join cte on cte.PaymentId = y.PaymentId
The output of the above t-sql CTE query will result as follows :
