About automatic matching problem-CodePudding

For example, I have two pens amount
1. On March 1, 100
2. On March 2, 200

One of 150

How can become

1. On March 1, 100100
2. On March 2, 200, 50
.,,,,,,,

CodePudding user response:

This match what rules? I can't be,75 75 or 10140 etc? Or collection is another table

CodePudding user response:

reference 1/f, big watermelon cut a piece of a kilo of reply:

this match what rules? I can't be,75 75 or 10140 etc? Or collection is also a list

Payment is in another table, rules are in date order, has been reduced to each sum to zero

CodePudding user response:

 

The CREATE TABLE # T 
(ID INT IDENTITY (1, 1), 
DT DATE, 
The AMOUNT INT) 

INSERT INTO # T 
SELECT '2021-03-01', 80 UNION ALL 
SELECT '2021-03-02', 100 UNION ALL 
SELECT '2021-03-03', 200 

DECLARE @ RCV INT 

The SET @ RCV=150 

; WITH CTE_1 
AS 
(SELECT *, ROW_NUMBER () OVER (ORDER BY DT) AS an RN FROM # T), 

CTE_2 
AS 
(SELECT * FROM CTE_1 A 
The OUTER APPLY (SELECT SUM (AMOUNT) AS SUBTOTAL FROM CTE_1 WHERE RN<=A.R N) AS B) 

SELECT ID, DT, AMOUNT, 
The CASE WHEN SUBTOTAL<=@ RCV THEN AMOUNT 
The WHEN SUBTOTAL> @ RCV AND SUBTOTAL - AMOUNT<=@ RCV THEN @ RCV + AMOUNT - SUBTOTAL 
The ELSE 0 END AS ALLOCATE_AMOUNT 
The FROM CTE_2