USE Hospital
GO
CREATE VIEW ReportMedicine_v
AS
SELECT m.MedId AS 藥品編號, m.MedName AS 藥品名稱, 
      (CASE WHEN m.Flag = 1 THEN '中藥' ELSE '西藥' END) AS 類別, 
      b1.TypeName AS 分類, b.TypeName AS 單位, m.BuyPrice AS 進貨價格, 
      m.SalePrice AS 銷售價格, m.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 - m.BuyPrice * p.PNum) ELSE 0 END) 
      AS 純利潤
FROM Medicine m, BaseType b, BaseType b1,
          (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 = 1 AND ps.RegId = r.RegId
         GROUP BY ps.ItemId, ps.RegId, LEFT(r.RegDate,7)) p
WHERE m.MedId = p.ItemId AND m.UnitId = b.Id AND m.TypeId = b1.Id
GO