USE [PM]
GO
/****** 对象: StoredProcedure [dbo].[LfangSatstics] 脚本日期: 08/24/2013 10:57:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[LfangSatstics]
(
@begintime datetime,
@OverTime datetime,
@PName nvarchar(100)
)
AS
BEGIN
DECLARE @str nvarchar(2000)
SET @str=''
CREATE table #T
(
proId int,
gid int,
allMoney money
)
--插入数据：
SET @str=@str+'insert into #T(proId,gid,allMoney) SELECT ProjectId,Id,AllMoney FROM View_LFangStastics where IsSD=''是'' '
if(@PName<>'')
set @str=@str+' and PName LIKE ''%'' + REPLACE(''' + @PName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''
if(@begintime<>'')
set @str=@str+' and SignTime>='''+convert(varchar(100),@begintime)+''''
if(@OverTime<>'')
set @str=@str+' and SignTime<='''+convert(varchar(100),@OverTime)+''''
EXEC (@str)
--统计数据：
SELECT CASE WHEN t.tag='2' THEN '单个项目（汇总）' WHEN t.tag='1' THEN '所有项目（汇总）' ELSE v.PName END as ProName,v.GName,t.allmoney FROM
(SELECT proId,gid, SUM(allMoney) as allmoney,CASE WHEN GROUPING(gid)=1 AND GROUPING(proId)=1 THEN '1' WHEN
GROUPING(gid)=1 THEN '2' ELSE '' END as tag
FROM #T GROUP BY proId,gid WITH ROLLUP) as t LEFT join View_LFangStastics as v on
t.proId=v.ProjectId AND t.gid=v.Id
--删除临时表
DROP TABLE #T
END