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