Recognize, and a summary of water SQL statements-CodePudding

Known a detailed material in and out of the table below, each material has color, unit, the warehouse to distinguish, in the beginning, the beginning inventory is zero, for every material data in and out of the summary is as follows:

 CREATE TABLE # tempDetail (document type VARCHAR (20), warehouse VARCHAR (20), 
Batch number VARCHAR (20), the unit VARCHAR (20), sizes VARCHAR (20), material code VARCHAR (20), material name VARCHAR (20), 
Date Date, inventory number decimal, dispatch a decimal number) 
INSERT INTO # tempDetail values (' finished product warehousing, 'raw materials warehouse,' ', 'PCS', 'red', '03000001', 'power', '2021-03-01', 1000, 0) 
INSERT INTO # tempDetail values (' finished product warehousing, 'finished product storehouse,' ', 'PCS', 'white', '03000001', 'power', '2021-03-01', 500, 0) 
INSERT INTO # tempDetail values (' internal acquisition ', 'raw materials warehouse,' ', 'PCS', 'red', '03000001', 'power', '2021-03-02', 0, 10) 
INSERT INTO # tempDetail values (' internal acquisition ', 'finished goods warehouse', ' ', 'PCS', 'white', '03000001', 'power', '2021-03-02', 0, 30) 

INSERT INTO # tempDetail values (' sales outbound order ', 'raw materials warehouse,' ', 'PCS', 'red', '03000001', 'power', '2021-03-03', 0 40) 
INSERT INTO # tempDetail values (' sales outbound order ', 'raw materials warehouse,' ', 'PCS', 'red', '03000001', 'power', '2021-03-04', 0, 50) 

Select * from # tempDetail 
The following conclusions have 
Type of document warehouse batch unit sizes material code name happen date initial quantity inventory quantity dispatch quantity balance quantity 
Storing raw materials warehouse 03000001 power supply to the 2021-03-01, 1000, 1000 PCS red 
Storing finished products warehouse 03000001 power supply to the 2021-03-01, 500, 500 PCS white 
Internal acquisition raw material warehouse PCS red 03000001 power 2021-03-02, 1000 10 990 
Internal picking finished product storehouse 03000001 power the 2021-03-02 500 0, 30, 470 PCS white 
Sales outbound order raw materials warehouse 03000001 PCS red power the 2021-03-03 990 0 40 950 
Sales outbound order raw materials warehouse 03000001 power the 2021-03-04 470 0, 50, 420 PCS red