How to calculate the amount to be written off for multiple orders and the amount to be written off

How to calculate the amount to be written off for multiple orders and the amount to be written off

This article describes the entire process of calculating the amount of write-off for multiple orders. Run the database environment: SQL SERVER 2005, and share it with you below.

Is an order list, the existing amount is 1700, according to the order of order number in order to write off the order amount.

When order 6 is paid, the payment amount is insufficient and only 200 can be paid. The amount of the subsequent order is 0.

1. Basic data preparation

CREATE TABLE #t ( id INT ,dingdan VARCHAR(20),sale MONEY) INSERT INTO #t VALUES (1,'a',100);INSERT INTO #t VALUES (2,'b',200);  INSERT INTO #t VALUES (3,'c',300);  INSERT INTO #t VALUES (4,'d',400);  INSERT INTO #t VALUES (5,'e',500);  INSERT INTO #t VALUES (6,'f',600);  INSERT INTO #t VALUES (7,'g',700);INSERT INTO #t VALUES (8,'h',800);INSERT INTO #t VALUES (9,'i',900);INSERT INTO #t VALUES (10,'j',1000);

SolutionsAs follows:

Calculate the total amount of money to be written off before each order, and then add the amount of the order to be written off, which is compared with 1700,

If the sum is less than or equal to 1700, the order amount of this order can be all written off. Otherwise, only the portion can be written off,

That is, the sum of the amount of all orders before this order.

; WITH x1 AS (SELECT t1.id, t1.dingdan, t1.sale, (select isnull (SUM (t2.sale), 0) FROM # t t2 WHERE t2.id <t1.id) AS curr_sale_sum -- the amount of all orders before this order FROM # t t1),/* calculate the amount of write-off */x2 AS (SELECT id, dingdan, sale, case when curr_sale_sum + sale <= 1700 THEN sale ELSE 1700-curr_sale_sum end as new_sale FROM x1)/* If the write-off amount is negative, it is changed to 0 */SELECT id AS number, dingdan order, sale order amount, case when new_sale <0 THEN 0 ELSE new_sale end as write-off amount FROM x2

Because we cannot use analysis functions to solve problems, we have to use scalar quantum queries to achieve the same effect. Of course, the provided data has certain limitations,

If the serial number is not continuous, it cannot be solved by directly applying the SQL statement. You need to generate a continuous serial number yourself.

The result is as follows:

The above is all the solutions for calculating the amount of write-off for multiple orders. I hope this will be helpful for your learning.