Inventory information: product number, inventory quantity
Outbound records: the product number, order number, the delivery, the outbound date
Inventory records: product number, number of warehousing, warehousing date
Broken record: product number, the number of broken, broken date
Daily reports to get the specified date is as follows: request contains all records in the inventory information
Product number, product name, product series, the inventory quantity, delivery, inventory quantity, damaged number
DLB102-300 - F, la spectacular herb skin tonic 300 g moisturizing lotion, bottles of shower gel, 110,10,70,2,
DLB103-300 - F, la spectacular herbaceous aroma care skin tonic 300 g bath dew, bottles of shower gel, 1654,21,432,6,
DLB104-300 - F, la spectacular herbal skin tonic and pure and fresh shu shuang 300 g bath dew, bottles of shower gel, 263,18,43,,
DLB105-900 - F, la much appearance, herbal moisturizing body wash bottles of shower gel, 2916,,,,
DLB106-900 - F, la herbaceous aroma care lotion, bottles of shower gel, 3815,14,45,5,
DLB108-2000 - F, la much appearance, herbal moisturizing body wash bottles of shower gel, 90,,,,
DLB109-2000 - F, la herbaceous aroma care lotion, bottles of shower gel, to 10,,,,
DLB110-2000 - F, aromatic herbs and pure and fresh shu shuang bath dew, bottles of shower gel, 120,,,,
DLB202-900 - F, la varied floral comfortable bath dew, cold bottles of shower gel, 1488,,,,
DLB203-900 - F, la spectacular flowers nutrition tender bath dew, bottles of shower gel, 1735,,,,
.
Statements that day I do, I can't date specified, and cannot be stored procedures, as long as the conditions specified date, the left outer join can't return all the records in the inventory information
Refer to
Thank you very much!
- DROP the VIEW stock_view
- today's inventory record
/*
The CREATE VIEW stock_view
AS
SELECT TOP 100 PERCENT product. Pid, the product. The pname,. The product series, the inventory. Squantity
FROM the product
Right outer join the inventory
On the inventory. Pid=product. Pid
*/
- SELECT * FROM stock_view
- today's outbound records
- DROP the VIEW tadayout_view
/*
The CREATE VIEW tadayout_view
AS
SELECT *
The FROM stockout where DateDiff (day, outdate, getdate ())=0
*/
- SELECT * FROM tadaybre_view
- today's inventory records
/*
The CREATE VIEW tadayadd_view
AS
SELECT *
The FROM stockin where DateDiff (day, indate, getdate ())=0
*/
- today's broken record
/*
The CREATE VIEW tadaybre_view
AS
SELECT *
The FROM breakage where DateDiff (day, bdate, getdate ())=0
*/
- report
/*
The CREATE VIEW tadaystock_view
AS
SELECT TOP 100 PERCENT stock_view pid, stock_view. Pname, stock_view. Series,
Stock_view squantity, tadayout_view outquantity, tadayadd_view. Inquantity, tadaybre_view. Bquantity
The FROM stock_view
The LEFT OUTER JOIN tadayout_view ON stock_view. Pid=tadayout_view. Pid
The LEFT OUTER JOIN tadayadd_view ON stock_view. Pid=tadayadd_view. Pid
The LEFT OUTER JOIN tadaybre_view ON stock_view. Pid=tadaybre_view. Pid
The ORDER BY stock_view. Series
*/
CodePudding user response: