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. It details the product, quantity, color, and size of the order.
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. It details the products, quantities, colors, and sizes 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
