Number of products warehouse
A P001 100
A P002 200
B P001 120
A
Number of products a warehouse
A P001 50
P001 30 B
Income
Number of products a warehouse
A P001 10
A P002 20
C P001 15
C P003 10
Request the results
Warehouse products beginning from income balance
A P001 100 50 10 60
A P002 200 0 20 220
B P001 120 30 0, 90
15 15 C P001 0 0
C P003 0 0 10 10
CodePudding user response:
DECLARE @ initial TABLE (warehouse NVARCHAR (10) NOT NULL, product NVARCHAR (10) NOT NULL, number of INT the NOT NULL)
INSERT @ beginning (warehouse, product, quantity)
VALUES (' A ', 'P001', 100), (' A ', 'P002, 200), (' B', 'P001' 120)
DECLARE @ a TABLE (warehouse NVARCHAR (10) NOT NULL, product NVARCHAR (10) NOT NULL, a number of INT the NOT NULL)
INSERT @ issued (warehouse, product, quantity)
VALUES (' A ', 'P001, 50), (' B', 'P001, 30)
DECLARE @ income TABLE (warehouse NVARCHAR (10) NOT NULL, product NVARCHAR (10) NOT NULL, a number of INT the NOT NULL)
INSERT @ income (warehouse, product, quantity)
VALUES (' A ', 'P001, 10), (' A', 'P002, 20),
(' C ', 'P001, 15), (' C', 'P003, 10)
, which requires the results
Beginning from income balance - warehouse products
- A P001 100 50 10 60
- A P002 200 0 20 220
120 - B P001 30 0, 90
15 15 - C P001 0 0
- C P003 0 0 10 10
SELECT p. warehouse, p. products, COALESCE (p. beginning, 0) at the beginning, COALESCE (p., 0) * 1,
COALESCE (p. income, 0) income, COALESCE (p. beginning, 0) + COALESCE (p., 0) + COALESCE (p. income, 0) balance the FROM (
SELECT the warehouse, the products, quantity, 'initial' AS category FROM @ initial
UNION ALL SELECT warehouse, products, - a number, 'a' FROM @ issued
UNION ALL SELECT warehouse, products, quantity, 'income' income FROM @) list the PIVOT (SUM (quantity) FOR category IN (at the beginning, and income)) p
The ORDER BY p. warehouse, p. products
