All geek questions in one place

Performing Subtraction in T-SQL

I am sitting with two tables (although these are temporary tables) looking like this:

CREATE TABLE [dbo].[Invoice] ( [InvoiceId] [int] NOT NULL, [ReceiverId] [int] NOT NULL, [Amount] [numeric](19, 2) NOT NULL, [Priority] [int] NOT NULL ); GO CREATE TABLE [dbo].[Payment] ( [PaymentId] [int] NOT NULL, [SenderId] [int] NOT NULL, [Amount] [numeric](19, 2) NOT NULL ); GO

The data may look something like this:

Score

 InvoiceId ReceiverId Amount Priority 1 1 100.00 1 2 1 100.00 2 3 2 100.00 1 4 2 100.00 2 5 1 200.00 3

Payment

 PaymentId SenderId Amount 1 1 50.00 2 1 45.00 3 2 95.00 4 2 105.00

Incoming payments are stored in Payment . My code task distributes Payment.Amount between sending accounts.

The key to the relationship between them is ReceiverId and SenderId .

The Priority column is unique to each ReceiverId , and a value of "1" has a higher priority than "2".

A Payment line with SenderId "1" can be used on an infinite number of invoices with ReceiverId "1" - if there is not enough for them in the Payment.Amount column, they will be paid in accordance with their Priority .

I am trying to think of a way to program this without using a loop or cursor. Any suggestions? (I'm sitting on SQL Server 2014).

My expected result:

 1) Payment 1 and 2 would be used to partially pay Invoice 1. 2) Payment 3 would be used to partially pay Invoice 3. 3) Payment 4 would then complete invoice 3. 4) Payment 4 would then completely pay invoice 4. 5) Invoice 2 and 5 would be left completely unpaid.
+1
source share
1 answer

main idea

Think about your dollar spacing on a number line. Place your invoices and payments in the correct order on the line adjacent to each other.

Invoices, Recipient / Sender ID = 1

 |----100---|----100---|--------200--------|-----------> 0 100 200 400 ID 1 2 5

Payments, Recipient / Sender ID = 1

 |-50-|-45|--------------------------------------------> 0 50 95 ID 1 2

Put both sets of intervals together (cross them):

 |----|---|-|----------|-------------------|-----------> 0 50 95 100 200 400

Now you have the intervals:

 From To InvoiceID PaymentID ------------------------------------ 0 50 1 1 50 95 1 2 95 100 1 100 200 2 200 400 5

Invoices, Recipient / Sender ID = 2

 |----100---|----100---|-------------------------------> 0 100 200 ID 3 4

Payments, recipient / sender ID = 2

 |--95----|-----105----|-------------------------------> 0 95 200 ID 3 4

Put both sets of intervals together (cross them):

 |--------|-|----------|-------------------------------> 0 95 100 200

Now you have the intervals:

 From To InvoiceID PaymentID ------------------------------------ 0 95 3 3 95 100 3 4 100 200 4 4

For each of these intervals there can be no more than one invoice and no more than one payment (they also cannot be). Find which account and payment correspond to each of these intervals, and you have a comparison between your accounts and payments. Summarize all payment intervals for each invoice and you will find out if the payment was paid in full or in part.

The construction of the initial list of intervals separately for invoices and payments is made by performing the total amount.

 SUM(Amount) OVER (PARTITION BY ReceiverId ORDER BY Priority ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS InvoiceInterval SUM(Amount) OVER (PARTITION BY SenderId ORDER BY PaymentID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PaymentInterval

The intersection of these two sets is a simple UNION .

For each interval, find the corresponding invoice and payment. One easy way to do this is to use a subquery in OUTER APPLY .

Let it all be together.

Data examples

 DECLARE @Invoice TABLE ( [InvoiceId] [int] NOT NULL, [ReceiverId] [int] NOT NULL, [Amount] [numeric](19, 2) NOT NULL, [Priority] [int] NOT NULL ); DECLARE @Payment TABLE ( [PaymentId] [int] NOT NULL, [SenderId] [int] NOT NULL, [Amount] [numeric](19, 2) NOT NULL ); INSERT INTO @Invoice(InvoiceId,ReceiverId,Amount,Priority) VALUES (1, 1, 100.00, 1), (2, 1, 100.00, 2), (3, 2, 100.00, 1), (4, 2, 100.00, 2), (5, 1, 200.00, 3); INSERT INTO @Payment(PaymentId, SenderId, Amount) VALUES (1, 1, 50.00), (2, 1, 45.00), (3, 2, 95.00), (4, 2, 105.00);

Query

 WITH CTE_InvoiceIntervals AS ( SELECT I.InvoiceId ,I.ReceiverId AS ClientID ,I.Priority ,SUM(I.Amount) OVER (PARTITION BY I.ReceiverId ORDER BY I.Priority ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS InvoiceInterval FROM @Invoice AS I ) ,CTE_PaymentIntervals AS ( SELECT P.PaymentId ,P.SenderId AS ClientID ,P.PaymentId AS Priority ,SUM(P.Amount) OVER (PARTITION BY P.SenderId ORDER BY P.PaymentID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PaymentInterval FROM @Payment AS P ) ,CTE_AllIntervals AS ( SELECT ClientID ,InvoiceInterval AS Interval FROM CTE_InvoiceIntervals UNION SELECT ClientID ,PaymentInterval AS Interval FROM CTE_PaymentIntervals ) SELECT * FROM CTE_AllIntervals OUTER APPLY ( SELECT TOP(1) CTE_InvoiceIntervals.InvoiceId FROM CTE_InvoiceIntervals WHERE CTE_InvoiceIntervals.ClientID = CTE_AllIntervals.ClientID AND CTE_InvoiceIntervals.InvoiceInterval >= CTE_AllIntervals.Interval ORDER BY CTE_InvoiceIntervals.InvoiceInterval ) AS A_Invoices OUTER APPLY ( SELECT TOP(1) CTE_PaymentIntervals.PaymentId FROM CTE_PaymentIntervals WHERE CTE_PaymentIntervals.ClientID = CTE_AllIntervals.ClientID AND CTE_PaymentIntervals.PaymentInterval >= CTE_AllIntervals.Interval ORDER BY CTE_PaymentIntervals.PaymentInterval ) AS A_Payments ORDER BY ClientID ,Interval;

Result

 +----------+----------+-----------+-----------+ | ClientID | Interval | InvoiceId | PaymentId | +----------+----------+-----------+-----------+ | 1 | 50.00 | 1 | 1 | | 1 | 95.00 | 1 | 2 | | 1 | 100.00 | 1 | NULL | | 1 | 200.00 | 2 | NULL | | 1 | 400.00 | 5 | NULL | | 2 | 95.00 | 3 | 3 | | 2 | 100.00 | 3 | 4 | | 2 | 200.00 | 4 | 4 | +----------+----------+-----------+-----------+
+3
source

Source: https://habr.com/ru/post/1258168/

More articles:


All Articles