First-in-first-out data reversal

First-in-first-out data reversal

-- Sample data:
Create Table sales order (Ticket No. varchar (10), date datetime, Goods Code varchar (10), quantity INT)
Insert sales order
Select 'a1', '2017-5-1 ', 'abc', 2005 Union all
Select 'a2 ', '2014-5-3', 'abc', 2005 Union all
Select 'a3 ', '2014-5-4', 'abc', 2005 Union all
Select 'a4 ', '2017-5-8', 'abc', 2005

Create Table sales invoice (Ticket No. varchar (10), date datetime, product code varchar (10), quantity INT)
Insert sales invoice
Select 'b1 ', '2017-5-05', 'abc', 2005 Union all
Select 'b2', '2017-5-07', 'abc', 2005 Union all
Select 'b3', '2017-5-10', 'abc', 100'
Go

Problem description:
For the sales order and Delivery Order in the example data, the customer cannot specify the order to be sent this time because the customer is doing the sales and delivery. Therefore, the sales order cannot be associated with the sales invoice. The customer requires that the execution of the sales order be recorded in FIFO mode. For the sample data, the required results are as follows:
Bill No. Date Goods code order quantity delivery quantity to be executed quantity
---------------------------------------------------------------------------------------
A1 ABCD 100 100 0
A2 ABCD 200 200 0
A3 ABCD 300 200 100
A4 ABCD 500 0 500
Total 1100 500 600

-- Query Processing Process
Select
A. Ticket No., A. Date, A. Goods code,
Order Quantity = A. quantity,
Shipment quantity = isnull (case when a. s quantity <B. Quantity then A. Quantity else B. Quantity-a. p quantity end, 0 ),
Number of instances to be executed = case when a. s quantity <B. Quantity then 0 else a. s quantity-isnull (B. quantity, A. P quantity) End
Into # From (
Select ticket No., date = convert (char (10), date, 120), goods code,
Quantity = sum (Quantity ),
P quantity = isnull (select sum (Quantity) from sales order
Where goods code = A. Goods Code
And (datediff (day, date, min (A. Date)> 0
Or datediff (day, date, min (A. Date) = 0 and single number <A. Single number), 0 ),
S quantity = isnull (select sum (Quantity) from sales order
Where goods code = A. Goods Code
And (datediff (day, date, min (A. Date)> 0
Or datediff (day, date, min (A. Date) = 0 and single number <= A. Single number), 0)
From sales order
Group by ticket No., convert (char (10), date, 120), product code
)
Left join (
Select Goods code, quantity = sum (Quantity)
From sales invoice
Group by product code
) B on A. Goods code = B. Goods Code
And a. p quantity <B. Quantity
Order by A. Ticket No., A. Date, A. Goods Code

Select * from #
Union all
Select '','', 'Total', sum (order quantity), sum (shipment quantity), sum (quantity to be executed) from #
Drop table #

Original post address