求目录下各个项目中关闭事务的数量,事务的平均处理时间(从创建到关闭的平均时间)
select ProjectCode,ProjectName,count(ProblemID) as [已关闭事务数],AVG(datediff(hour,CreateTime,CloseTime)) as [平均关闭时间(时)],
AVG(datediff(day,CreateTime,CloseTime)) as [平均关闭时间(天)]
FROM v_Pts_Problems
WHERE ProjectID IN (SELECT ProjectID FROM Pts_Projects WHERE ProjectCatalogID =项目目录ID)
group by ProjectID,ProjectCode,ProjectName
ORDER BY AVG(datediff(hour,CreateTime,CloseTime)) desc