How to calculate the write-off amount for multiple orders _mssql2005

This article describes the entire process of calculating the amount of write-off for multiple orders, running the database environment: SQL SERVER .

The following figure is a list of orders, the existing amount to 1700, according to order number of orders in order to cancel the order amount.

To pay the order 6 o'clock, the payment amount is insufficient, can only pay 200, after the cancellation amount of the order is 0.

1. Basic Data Preparation

CREATE TABLE #t 
(id INT,
dingdan VARCHAR,
sale
) 

INSERT into #t VALUES (1, ' a ', m);
INSERT into #t VALUES (2, ' B ', m);  
INSERT into #t VALUES (3, ' C ', +);  
INSERT into #t VALUES (4, ' d ',);  
INSERT into #t VALUES (5, ' E ', m);  
INSERT into #t VALUES (6, ' f ',);  
INSERT into #t VALUES (7, ' G ', m);
INSERT into #t VALUES (8, ' h ',);
INSERT into #t VALUES (9, ' I ', 900);
INSERT into #t VALUES (Ten, ' J ', 1000);

The idea of solving problems is as follows:

Calculate the total amount to be written off before each order, and then add the order amount that will be written off, compared with 1700,

If the sum is less than or equal to 1700, then the order amount of this order can be all written off, otherwise, only part of the write-off,

That is 1700-the sum of all order amounts prior to 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--all order amounts before this order from   #t t1
       ), * Calculate the Write-off amount *
    /x2 as
     (SELECT  ID,
            dingdan,
            sale, case when
            curr_sale_sum + sale <= 1700 the N Sale
               ELSE 1700-curr_sale_sum end as
            new_sale   from X1
       )/
   cancellation amount is negative, change to 0*/
  SELECT ID as ordinal,
      dingdan order,
      Sale Order Amount, case when
      New_sale < 0 THEN 0
         ELSE New_sale end as
      write-off gold Amount
  from  x2

Because the analytic function can not be used to solve problems, we have to use standard quantum query to achieve the same effect. Of course, the data provided has certain limitations,

If the serial number is not continuous, the direct use of my SQL can not be resolved, you need to generate a sequential serial number.

The results are as follows:

The above is about the calculation of multiple orders of the write-off amount of all problem solving ideas, I hope to help you learn.