URTracker

insert into @tb_create select CreateUserID,count(1) from Pts_Problems Where ProjectID=项目ID group by createuserid

--select * from @tb_create

--分配的需求总数(从已提交到开发实现 步骤的待办人参数
declare @tb_dev table(UserID int, PCount int)
insert into @tb_dev
 select AssignTo, count(distinct ProblemID) from Pts_Records where RecordClass=1 AND SrcStateID=分配需求前状态ID AND StateID=分配需求后状态ID and assignto > 0 group by AssignTo
--select * from @tb_dev

-- 开发的需求总数（从开发实现到待测试状态的操作人）
declare @tb_devdone table(UserID int, PCount int)
insert into @tb_devdone
 select CreateUser, count(distinct ProblemID) from Pts_Records where RecordClass=1 AND SrcStateID=开发状态的ID AND StateID=待测试状态的ID group by createUser

-- 测试需求数（从测试状态到其他状态处理记录的提交人）
declare @tb_test table(UserID int, PCount int)
insert into @tb_test
 select CreateUser, count(distinct ProblemID) from Pts_records where RecordClass=1 AND SrcStateID=测试状态的ID group by CreateUser

-- 取出用户姓名和数量（只显示数量不全为0的人员）
select u.UserID as [用户ID],
 u.DisplayName as [姓名],
 cr.[PCount] as [新建需求数],
 [@tb_dev].PCount as [分配的需求总数],
 [@tb_devdone].PCount as [开发的需求总数],
 [@tb_test].PCount as [测试的需求总数]
FROM
 Accounts_Users u left outer join @tb_create cr on u.UserID = cr.UserID
 left outer join @tb_dev on u.UserID = [@tb_dev].UserID
 left outer join @tb_devdone on u.UserID = [@tb_devdone].UserID
 left outer join @tb_test on u.UserID = [@tb_test].UserID
where
 cr.[PCount] > 0 OR [@tb_dev].PCount > 0 OR [@tb_devdone].PCount>0 OR [@tb_test].PCount>0
ORDER BY u.DisplayName