valid.sql

库存管理系统

USE Stock
GO
CREATE VIEW dbo.Valid
AS
SELECT dbo.ProInStore.SpId AS 库存记录编号, dbo.Product.Pname AS 产品名称, 
      dbo.ProInStore.Pprice AS 产品价格, dbo.ProInStore.Pnum AS 产品数量, 
      dbo.ProInStore.MakeDate AS 生产日期, dbo.Storehouse.Sname AS 仓库名称, 
      ROUND(DATEDIFF(day, DATEADD(day, dbo.Product.Valid, dbo.ProInStore.MakeDate), 
      GETDATE()), 0) AS 距离失效期的天数
FROM dbo.ProInStore INNER JOIN
      dbo.Product ON dbo.ProInStore.Pid = dbo.Product.Pid AND DATEDIFF(day, 
      GETDATE(), dbo.ProInStore.MakeDate) 
      >= dbo.Product.Valid - dbo.Product.AlarmDays INNER JOIN
      dbo.Storehouse ON dbo.ProInStore.Sid = dbo.Storehouse.Sid
GO