-- 待办事务数
declare @t_assign table
(
UserID int,
AssignCount int
)
insert into @t_assign
select AssignedTo,count(1) FROM v_Pts_Problems
WHERE IsClosed=0 AND AssignedTo >0
group by AssignedTo
--select * from @t_assign
-- 创建事务数
declare @t_create table
(
UserID int,
CreateCount int
)
insert into @t_create
select CreateUserID,count(1) FROM Pts_Problems
--WHERE CreateTime >= @StartTime AND CreateTime <@EndTime
group by CreateUserID
-- 参与过的事务数
declare @t_process table
(
UserID int,
ProcessCount int
)
insert into @t_process
SELECT CreateUser,count(Distinct ProblemID) From Pts_Records
--WHERE DateCreated >= @StartTime AND DateCreated <@EndTime
group by CreateUser
-- 提交的知识库文章数
declare @t_article table
(
UserID int,
ArticleCount int
)
insert into @t_article
select CreateUserID,count(1) FROM Kb_Articles
--WHERE CreateDate >= @StartTime AND CreateDate <@EndTime
GROUP BY CreateUserID
-- output
Select d.DeptName as '部门',
u.DisplayName '姓名',
[@t_assign].AssignCount as '当前待办数',
[@t_create].CreateCount as '创建事务数',
[@t_process].ProcessCount as '参与事务数',
[@t_article].ArticleCount as '创建文章数'
FROM Accounts_Users u
left outer join Accounts_Department d on u.DeptID = d.DeptID
left outer join @t_assign on u.UserID = [@t_assign].UserID
left outer join @t_create on u.UserID = [@t_create].UserID
left outer join @t_process on u.UserID = [@t_process].UserID
left outer join @t_article on u.UserID = [@t_article].UserID
WHERE NOT ([@t_assign].AssignCount is null AND [@t_create].CreateCount is null AND [@t_process].ProcessCount is null AND [@t_article].ArticleCount is null)
ORDER BY d.DeptName, u.DisplayName