-- 查询各部门在各项目中的参与情况
-- 待办事务数
declare @t_assign table
(
DeptID int,
ProjectID int,
AssignCount int
)
-- 创建事务数
declare @t_create table
(
DeptID int,
ProjectID int,
CreateCount int
)
-- 参与过的事务数
declare @t_process table
(
DeptID int,
ProjectID int,
ProcessCount int
)
declare @DeptID int
declare dept_cursor cursor for
select DeptID FROM Accounts_Department
open dept_cursor
fetch next from dept_cursor into @DeptID
WHILE @@FETCH_STATUS = 0
BEGIN
---- 开始循环
insert into @t_assign
select @DeptID,ProjectID,count(1) FROM v_Pts_Problems WHERE IsClosed=0 AND AssignedTo in (SELECT UserID FROM Accounts_Users WHERE DeptID=@DeptID)
group by ProjectID
--select * from @t_assign
insert into @t_create
select @DeptID,ProjectID,count(1) FROM Pts_Problems WHERE CreateUserID IN (SELECT UserID FROM Accounts_Users WHERE DeptID=@DeptID)
--AND CreateTime >= @StartTime AND CreateTime <@EndTime
group by ProjectID
insert into @t_process
SELECT @DeptID,Pts_Problems.ProjectID,count(distinct Pts_Problems.ProblemID) From Pts_Records left outer join Pts_Problems on Pts_Records.ProblemID = Pts_Problems.ProblemID
Where Pts_Records.CreateUser IN (SELECT UserID FROM Accounts_Users WHERE DeptID=@DeptID)
--AND Pts_Records.DateCreated >= @StartTime AND Pts_Records.DateCreated <@EndTime
group by Pts_Problems.ProjectID
FETCH NEXT FROM dept_cursor INTO @DeptID
---- 结束循环
END --cursor
close dept_cursor
deallocate dept_cursor
-- output
Select
p.DeptName as '部门名称',
p.ProjectID as '项目ID',
p.Name '项目名称',
[@t_assign].AssignCount as '当前待办数',
[@t_create].CreateCount as '创建事务数',
[@t_process].ProcessCount as '参与事务数'
FROM
(select d.DeptID,d.DeptName,p.* from Accounts_Department d, Pts_Projects p) p
left outer join @t_assign on p.ProjectID = [@t_assign].ProjectID and p.DeptID = [@t_assign].DeptID
left outer join @t_create on p.ProjectID = [@t_create].ProjectID AND p.DeptID = [@t_create].DeptID
left outer join @t_process on p.ProjectID = [@t_process].ProjectID AND p.DeptID = [@t_process].DeptID
where NOT ([@t_assign].AssignCount is null AND [@t_create].CreateCount is null AND [@t_process].ProcessCount is null)
order by p.DeptName,p.Name