USE Hospital
GO
CREATE VIEW ReportDoctor_v
AS
SELECT d .Name AS 醫生姓名, b.TypeName AS 科室名稱, d .Title AS 職務, p.RegDate AS 月份,
      (CASE WHEN p.PNum >= 0 THEN p.PNum ELSE 0 END) AS 銷售數量, 
      (CASE WHEN p.PSum >= 0 THEN p.PSum ELSE 0 END) AS 銷售總額, 
      (CASE WHEN p.PEarn >= 0 THEN p.PEarn ELSE 0 END) AS 純利潤
FROM BaseType b, Doctor d,
          (SELECT DocId, LEFT(RegDate,7) AS RegDate, SUM(PNum) AS PNum, 
		SUM(PSum) AS PSum, SUM(PEarn) AS PEarn
         FROM PayList_v
         GROUP BY DocId, LEFT(RegDate,7)) p
WHERE b.TypeId = 1 AND d.DeptId = b.Id AND d.Id = p.DocId
GO