reportitems_v.sql

来自「医院管理系统」· SQL 代码 · 共 18 行

USE Hospital
GO
CREATE VIEW ReportItems_v
AS
SELECT i.ItemId AS 药品编号, i.ItemName AS 药品名称, b.TypeName AS 单位, 
      i.BuyPrice AS 成本价格, i.SalePrice AS 销售价格, i.Total AS 库存数量, p.RegDate AS 销售日期,
      (CASE WHEN p.PNum >= 0 THEN p.PNum ELSE 0 END) AS 销售数量, 
      (CASE WHEN p.PNum >= 0 THEN p.PSum ELSE 0 END) AS 销售总额, 
      (CASE WHEN p.PNum >= 0 THEN (p.PSum - i.BuyPrice * p.PNum) ELSE 0 END) 
      AS 纯利润
FROM Items i, BaseType b,
          (SELECT ps.ItemId, ps.RegId, LEFT(r.RegDate,7) AS RegDate, SUM(ps.PNum) AS PNum, 
               SUM(ps.Price * ps.PNum) AS PSum
         FROM PayItems ps, Registration r
         WHERE ps.PType = 2 AND ps.RegId = r.RegId
         GROUP BY ps.ItemId, ps.RegId, LEFT(r.RegDate,7)) p
WHERE i.ItemId = p.ItemId AND i.UnitId = b.Id
GO