valid.sql

用ｖｂ编的一个商品库存管理系统

CREATE VIEW dbo.Valid
AS
SELECT dbo.ProInStore.StoreProId AS 仓库存储编号, dbo.Product.ProName AS 产品名称, 
      dbo.ProInStore.ProPrice AS 产品价格, dbo.ProInStore.ProNum AS 产品数量, 
      dbo.ProInStore.CreateDate AS 生产日期, dbo.Storehouse.StoreName AS 仓库名称, 
      ROUND(DATEDIFF(day, DATEADD(day, dbo.Product.Valid, dbo.ProInStore.CreateDate), 
      GETDATE()), 0) AS 距离失效期的天数
FROM dbo.ProInStore INNER JOIN
      dbo.Product ON dbo.ProInStore.ProId = dbo.Product.ProId AND DATEDIFF(day, 
      GETDATE(), dbo.ProInStore.CreateDate) 
      >= dbo.Product.Valid - dbo.Product.AlarmDays INNER JOIN
      dbo.Storehouse ON dbo.ProInStore.StoreId = dbo.Storehouse.StoreId