我有一个交叉表查询,可以根据某个记录状态(活动、结束等)对所有记录进行很好的求和
Select
c.ReferralName,
Sum((d.CaseStatusName) = 'Active') As Active,
Sum((d.CaseStatusName) = 'Completed') As Completed,
Sum((d.CaseStatusName) = 'Submitted') As Submitted,
Count(b.CaseID) As Total,
Sum(b.LeadCost) As Cost
From
tblcontacts a Inner Join
tblcases b On a.ContactID = b.ContactID Inner Join
tblreferral c On c.RefferalID = a.ContactReferrelSource Inner Join
tblcasestatus d On d.CaseStatusID = b.CaseStatusName
Group By
c.ReferralName With Rollup
这很好,但我现在还想运行一个查询,用b.LeadCost的和替换d.casestusname的和,但是找不到实现它的方法!
我想我需要的是,如果d.CaseStatusName='Active',则b.LeadCost的总和,等等
非常感谢您的帮助。
最佳答案
Select
c.ReferralName,
Sum(CASE WHEN (d.CaseStatusName) = 'Active' THEN b.LeadCost ELSE 0 END) As Active,
Sum(CASE WHEN (d.CaseStatusName) = 'Completed' THEN b.LeadCost ELSE 0 END) As Completed,
Sum(CASE WHEN (d.CaseStatusName) = 'Submitted' THEN b.LeadCost ELSE 0 END) As Submitted,
Count(b.CaseID) As Total,
Sum(b.LeadCost) As Cost
From
tblcontacts a Inner Join
tblcases b On a.ContactID = b.ContactID Inner Join
tblreferral c On c.RefferalID = a.ContactReferrelSource Inner Join
tblcasestatus d On d.CaseStatusID = b.CaseStatusName
Group By
c.ReferralName With Rollup