Implementation result: CREATEVIEW [dbo]. [v_Show_StockInOrderItem] ASWITHcte_detailAS (-- number of imported records SELECTs. productID, s. colorsID, s. sizesID, s. title, s. colorsName, s. sizesName, s. quantityASqtyFROMTheBeerHouse. stockInItemssLEFTJOINTheBeerHouse.
Implementation result: create view [dbo]. [v_Show_StockInOrderItem] as with cte_detail AS (-- number of checked instances selected s. productID, s. colorsID, s. sizesID, s. title, s. colorsName, s. sizesName, s. quantity AS qty FROM TheBeerHouse. stockInItems s left join TheBeerHouse.
Effect:
Create view [dbo]. [v_Show_StockInOrderItem]
AS
WITH cte_detail
(
-- Number of warehouse receiving records
SELECT s. ProductID, s. ColorsID, s. SizesID, s. Title, s. ColorsName, s. SizesName, s. Quantity AS qty
FROM TheBeerHouse. StockInItems s
Left join TheBeerHouse. StockIn si ON si. StockInID = s. StockInID
WHERE si. Status = 'audited' -- the flag that has been reviewed,
-- TheBeerHouse. StockIn is equivalent to an order.
-- TheBeerHouse. StockInItems is associated with the order table. The specific product, quantity, color, and size of the order to be stored
UNION ALL
-- Warehouse picking quantity
SELECT o. ProductID AS oProductID, o. ColorsID AS oColorsID, o. SizesID AS oSizesID, o. Title AS otitle,
O. ColorsName AS oColorsName, o. SizesName AS oSizesName,-o. Quantity AS qty
FROM TheBeerHouse. OrderItems o
Left join TheBeerHouse. Orders oo ON oo. OrderID = o. OrderID
WHERE oo. ShippingStatus = 'applyshipped '-- a warehouse marker
-- TheBeerHouse. Orders
-- TheBeerHouse. OrderItems is associated with the order table. The product, quantity, color, and size of the order to be exported
)
-- Combine the above results
, Cte_qty
(
SELECT ProductID, SUM (qty) AS qty, Title, SizesID, SizesName, ColorsID, ColorsName -- qty indicates the inbound volume-outbound volume, that is, inventory margin
FROM cte_detail
WHERE ProductID = ProductID AND ColorsID = ColorsID AND SizesID = SizesID
Group by ProductID, Title, SizesID, SizesName, ColorsID, ColorsName
)
SELECT ROW_NUMBER () OVER (order by t. ProductID) AS 'data identified'
, Product ID = T. ProductID
, ROW_NUMBER () OVER (order by t. ProductID) AS 'sequence number'
, Product no. = T. ProductID
, Product name = T. Title
, Color = T. ColorsName
, Size = T. SizesName
, Total warehouse receiving amount = (select isnull (SUM (s. Quantity), 0)
FROM TheBeerHouse. StockInItems s
Left join TheBeerHouse. StockIn si ON si. StockInID = s. StockInID
WHERE si. Status = 'audited' AND s. ProductID = T. ProductID AND s. SizesID = T. SizesID AND s. ColorsID = T. ColorsID)
, Total sales = (select isnull (SUM (o. Quantity), 0)
FROM TheBeerHouse. OrderItems o
Left join TheBeerHouse. Orders oo ON oo. OrderID = o. OrderID
WHERE oo. ShippingStatus = 'applyshipped 'AND o. ProductID = T. ProductID AND o. SizesID = T. SizesID AND o. ColorsID = T. ColorsID)
, Inventory quantity = ISNULL (T. qty, 0)
FROM
Cte_qty AS T
GO