SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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
 ExpenseId int,
 ExpensedById int,
 ExpenseAmount int,
 ExpenseDate datetime
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
 group by
 y.ExpensedById, y.ExpenseId, y.ExpenseDate, y.ExpenseAmount,
 cte.PaymentId, cte.PaymentAmount, cte.PaymentDate PrePayments_date,
 total_difference = (total_payments - total_expenses)
from (
  PaymentId = (select MIN(PaymentId) from cte where total_expenses <= total_payments)
 from (
   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
 ) x
) y
left join cte on cte.PaymentId = y.PaymentId

The output of the above t-sql CTE query will result as follows :


SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.