现在有一个存储过程,运行的结果如下:
DeptId All Done Doing OverDue
4387 46 23 23 0
4393 33 33 0 0
4385 106 96 10 0
4615 14 14 0 0
4357 25 14 11 0现在需要把这个存储过程功能修改一下,统计All,Done,Doing,OverDue的总数,显示一行统计总数就可以了,DeptId自段也不需要了。
如下:
All Done Doing OverDue
224 180 44 0请问大侠,应该在哪些语句上进行修改,如何修改。CREATE PROCEDURE [dbo].[Proc_Stat_OAcpj_By_Dept]
@BeginTime DateTime, @EndTime DateTime
ASSelect
b.WF_WorklistID tag,
CON_DepartmentID DeptID
into #tbl_OA
from OA.dbo.m_wgjcpj a, OA.dbo.WF_Worklist b
where a.CON_RecordID = b.WF_RecordIDSelect
starttm, endtm, status, tag, updatetm, comments, type
into #tbl_Task
from WSModules.dbo.Task_Items
where starttm >= @BeginTime and starttm <= @EndTimeSelect
deptid, starttm, endtm, status, updatetm
into #tbl_Joined
from #tbl_OA a, #tbl_Task b
where a.tag = b.tag and deptid is not nullSelect DeptID, Count(*) Count
into #tbl_All
from #tbl_Joined
Group by Deptidselect substring(comments, 1, CHARINDEX (';', comments)-1) as DeptID
into #temp_tbl_Done
from #tbl_Task
where comments <> '' and comments is not null and [type]='呈批件流程'select DeptID, count(*) [Count]
into #tbl_Done
from #temp_tbl_Done
group by DeptIDSelect DeptID , Count(*) [Count]
into #tbl_Doing
from #tbl_Joined
where status <> N'已办'
Group by DeptidDECLARE @now DateTime
set @now = GetDate()Select DeptID, Count(*) [Count]
into #tbl_OverDue
from #tbl_Joined
where updatetm > endtm or (@now > endtm and status <> N'已办')
Group by DeptidSELECT
ISNULL(a.[DeptID],b.[DeptID]) as [DeptID],
ISNULL(a.[COUNT], 0) + ISNULL(b.[COUNT], 0) as [All],
ISNULL(b.[COUNT], 0) as [Done],
ISNULL(c.[COUNT], 0) as [Doing],
ISNULL(d.[COUNT], 0) as [OverDue]
FROM #tbl_All a
FULL OUTER JOIN #tbl_Done b ON a.Deptid = b.Deptid
FULL OUTER JOIN #tbl_Doing c ON a.Deptid = c.Deptid
FULL OUTER JOIN #tbl_OverDue d ON a.Deptid = d.Deptiddrop table #tbl_OA
drop table #tbl_All
drop table #tbl_Joined
drop table #tbl_Done
drop table #tbl_Doing
drop table #tbl_OverDueGO
DeptId All Done Doing OverDue
4387 46 23 23 0
4393 33 33 0 0
4385 106 96 10 0
4615 14 14 0 0
4357 25 14 11 0现在需要把这个存储过程功能修改一下,统计All,Done,Doing,OverDue的总数,显示一行统计总数就可以了,DeptId自段也不需要了。
如下:
All Done Doing OverDue
224 180 44 0请问大侠,应该在哪些语句上进行修改,如何修改。CREATE PROCEDURE [dbo].[Proc_Stat_OAcpj_By_Dept]
@BeginTime DateTime, @EndTime DateTime
ASSelect
b.WF_WorklistID tag,
CON_DepartmentID DeptID
into #tbl_OA
from OA.dbo.m_wgjcpj a, OA.dbo.WF_Worklist b
where a.CON_RecordID = b.WF_RecordIDSelect
starttm, endtm, status, tag, updatetm, comments, type
into #tbl_Task
from WSModules.dbo.Task_Items
where starttm >= @BeginTime and starttm <= @EndTimeSelect
deptid, starttm, endtm, status, updatetm
into #tbl_Joined
from #tbl_OA a, #tbl_Task b
where a.tag = b.tag and deptid is not nullSelect DeptID, Count(*) Count
into #tbl_All
from #tbl_Joined
Group by Deptidselect substring(comments, 1, CHARINDEX (';', comments)-1) as DeptID
into #temp_tbl_Done
from #tbl_Task
where comments <> '' and comments is not null and [type]='呈批件流程'select DeptID, count(*) [Count]
into #tbl_Done
from #temp_tbl_Done
group by DeptIDSelect DeptID , Count(*) [Count]
into #tbl_Doing
from #tbl_Joined
where status <> N'已办'
Group by DeptidDECLARE @now DateTime
set @now = GetDate()Select DeptID, Count(*) [Count]
into #tbl_OverDue
from #tbl_Joined
where updatetm > endtm or (@now > endtm and status <> N'已办')
Group by DeptidSELECT
ISNULL(a.[DeptID],b.[DeptID]) as [DeptID],
ISNULL(a.[COUNT], 0) + ISNULL(b.[COUNT], 0) as [All],
ISNULL(b.[COUNT], 0) as [Done],
ISNULL(c.[COUNT], 0) as [Doing],
ISNULL(d.[COUNT], 0) as [OverDue]
FROM #tbl_All a
FULL OUTER JOIN #tbl_Done b ON a.Deptid = b.Deptid
FULL OUTER JOIN #tbl_Doing c ON a.Deptid = c.Deptid
FULL OUTER JOIN #tbl_OverDue d ON a.Deptid = d.Deptiddrop table #tbl_OA
drop table #tbl_All
drop table #tbl_Joined
drop table #tbl_Done
drop table #tbl_Doing
drop table #tbl_OverDueGO
select sum([All]) [All],sum([Done]) [Done],sum([Doing])[Doing], sum([OverDue]) [OverDue]
from
(
SELECT
ISNULL(a.[DeptID],b.[DeptID]) as [DeptID],
ISNULL(a.[COUNT], 0) + ISNULL(b.[COUNT], 0) as [All],
ISNULL(b.[COUNT], 0) as [Done],
ISNULL(c.[COUNT], 0) as [Doing],
ISNULL(d.[COUNT], 0) as [OverDue]
FROM #tbl_All a
FULL OUTER JOIN #tbl_Done b ON a.Deptid = b.Deptid
FULL OUTER JOIN #tbl_Doing c ON a.Deptid = c.Deptid
FULL OUTER JOIN #tbl_OverDue d ON a.Deptid = d.Deptid
)aa
@BeginTime DateTime, @EndTime DateTime
AS Select
b.WF_WorklistID tag,
CON_DepartmentID DeptID
into #tbl_OA
from OA.dbo.m_wgjcpj a, OA.dbo.WF_Worklist b
where a.CON_RecordID = b.WF_RecordID Select
starttm, endtm, status, tag, updatetm, comments, type
into #tbl_Task
from WSModules.dbo.Task_Items
where starttm >= @BeginTime and starttm <= @EndTime Select
deptid, starttm, endtm, status, updatetm
into #tbl_Joined
from #tbl_OA a, #tbl_Task b
where a.tag = b.tag and deptid is not null Select DeptID, Count(*) Count
into #tbl_All
from #tbl_Joined
Group by Deptid select substring(comments, 1, CHARINDEX (';', comments)-1) as DeptID
into #temp_tbl_Done
from #tbl_Task
where comments <> '' and comments is not null and [type]='呈批件流程' select DeptID, count(*) [Count]
into #tbl_Done
from #temp_tbl_Done
group by DeptID Select DeptID , Count(*) [Count]
into #tbl_Doing
from #tbl_Joined
where status <> N'已办'
Group by Deptid DECLARE @now DateTime
set @now = GetDate() Select DeptID, Count(*) [Count]
into #tbl_OverDue
from #tbl_Joined
where updatetm > endtm or (@now > endtm and status <> N'已办')
Group by Deptid SELECT
ISNULL(a.[DeptID],b.[DeptID]) as [DeptID],
ISNULL(a.[COUNT], 0) + ISNULL(b.[COUNT], 0) as [All],
ISNULL(b.[COUNT], 0) as [Done],
ISNULL(c.[COUNT], 0) as [Doing],
ISNULL(d.[COUNT], 0) as [OverDue]
INTO #TEMPTB
FROM #tbl_All a
FULL OUTER JOIN #tbl_Done b ON a.Deptid = b.Deptid
FULL OUTER JOIN #tbl_Doing c ON a.Deptid = c.Deptid
FULL OUTER JOIN #tbl_OverDue d ON a.Deptid = d.Deptid SELECT
CASE WHEN GROUPING(DeptID) = 1 THEN '总计' ELSE DeptID END AS DeptID
,SUM([All]) AS [All]
,SUM(Done) AS Done
,SUM(Doing) AS Doing
,SUM(OverDue) AS OverDue
FROM #TEMPTB
GROUP BY
DeptID WITH ROLLUP
drop table #tbl_OA
drop table #tbl_All
drop table #tbl_Joined
drop table #tbl_Done
drop table #tbl_Doing
drop table #tbl_OverDue GO
from
(
SELECT
--ISNULL(a.[DeptID],b.[DeptID]) as [DeptID],
ISNULL(a.[COUNT], 0) + ISNULL(b.[COUNT], 0) as [All],
ISNULL(b.[COUNT], 0) as [Done],
ISNULL(c.[COUNT], 0) as [Doing],
ISNULL(d.[COUNT], 0) as [OverDue]
FROM #tbl_All a
FULL OUTER JOIN #tbl_Done b ON a.Deptid = b.Deptid
FULL OUTER JOIN #tbl_Doing c ON a.Deptid = c.Deptid
FULL OUTER JOIN #tbl_OverDue d ON a.Deptid = d.Deptid
)aa
试试这个
---生成临时表
SELECT AA = 1,
ISNULL(a.[DeptID],b.[DeptID]) as [DeptID],
ISNULL(a.[COUNT], 0) + ISNULL(b.[COUNT], 0) as [All],
ISNULL(b.[COUNT], 0) as [Done],
ISNULL(c.[COUNT], 0) as [Doing],
ISNULL(d.[COUNT], 0) as [OverDue]
INTO #TTT
FROM #tbl_All a
FULL OUTER JOIN #tbl_Done b ON a.Deptid = b.Deptid
FULL OUTER JOIN #tbl_Doing c ON a.Deptid = c.Deptid
FULL OUTER JOIN #tbl_OverDue d ON a.Deptid = d.Deptid -------统计
select [DeptID] = case when grouping([DeptID]) = 0 then [DeptID] else '合计' end
,[All]
,[Done]
,[Doing]
,[OverDue]
from #TTT
group by AA,[DeptID] with rollup
having grouping(AA) = 0
更正一下
---生成临时表
SELECT
ISNULL(a.[DeptID],b.[DeptID]) as [DeptID],
ISNULL(a.[COUNT], 0) + ISNULL(b.[COUNT], 0) as [All],
ISNULL(b.[COUNT], 0) as [Done],
ISNULL(c.[COUNT], 0) as [Doing],
ISNULL(d.[COUNT], 0) as [OverDue]
INTO #TTT
FROM #tbl_All a
FULL OUTER JOIN #tbl_Done b ON a.Deptid = b.Deptid
FULL OUTER JOIN #tbl_Doing c ON a.Deptid = c.Deptid
FULL OUTER JOIN #tbl_OverDue d ON a.Deptid = d.Deptid -------统计
select [DeptID] = case when grouping([DeptID]) = 0 then [DeptID] else '合计' end
,sum([All])
,sum([Done])
,sum([Doing])
,sum([OverDue])
from #TTT
group by [DeptID] with rollup