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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014



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 :

sql-tutorial-map-payments-to-expenses-tsql-problem






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems