可以创建一个简单报表,使用如下代码:
select ProjectCode,ProjectName,sum(case when ProblemID is not null then 1 else 0 end) [事务数],
sum(case when IsClosed=1 then 1 else 0 end) [已关闭数量],
cast(cast(sum(case when IsClosed=1 then 1 else 0 end)*1.0*100/sum(case when ProblemID is not null then 1 else 0 end) as decimal(10,2)) as varchar(50)) +'%' as [已关闭百分比],
sum(case when IsClosed=0 then 1 else 0 end) [跟踪中数量],
cast(cast(sum(case when IsClosed=0 then 1 else 0 end)*1.0*100/sum(case when ProblemID is not null then 1 else 0 end) as decimal(10,2)) as varchar(50)) +'%' as [跟踪中百分比]
FROM v_Pts_Problems
WHERE ProjectID IN (SELECT ProjectID FROM Pts_Projects WHERE ProjectCatalogID = 目录ID)
group by ProjectID,ProjectCode,ProjectName
ORDER BY ProjectName