写的比较好的存储过程备份（场所日统计）

USE 
GO
/****** Object:  StoredProcedure [dbo].[Task_MatrixMain_MachineDayStat]    Script Date: 2012/10/26 16:15:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*------------------------------------
-- 用途：处理设备日统计任务
-- 项目名称：Matrix
-- 说明：根据Matrix_sys_RefreshData表中MachineDayStats时间，按日统计所有设备热点
-- 时间：2012/09/06
-- 编写者: 
-- 测试结果：已测试通过
--------------------------------------
-- 修改记录:
-- 编号	修改时间	修改人	修改原因	修改标注

------------------------------------*/
/*
测试语句：
EXEC [Task_MatrixMain_MachineDayStat] @StatDate='2012-9-12 12:12:12',@Function=2
*/
ALTER PROCEDURE [dbo].[Task_MatrixMain_MachineDayStat]
	@StatDate DATETIME=NULL,--默认统计前一天的记录
	@Function INT=1  --1-统计并写表，2-统计但不写表，返回统计结果
AS
BEGIN
	
	SET NOCOUNT ON 

	DECLARE @SQL NVARCHAR(MAX),
			@DatabaseName NVARCHAR(20),
			@RouteTableName NVARCHAR(50),
			@UserTableName NVARCHAR(50),
			@DiffTime INT,
			@TableExists INT,
			@TableExists2 INT
	IF ISNULL(@StatDate,'')=''
		SET @StatDate=DATEADD(DD,-1,DATEDIFF(DD,0,GETDATE()))  
	SET @DatabaseName='MatrixLOG'+CONVERT(NVARCHAR(6),@StatDate,112)
	SET @RouteTableName='MatrixLOG_log_WifiRoute'+CONVERT(NVARCHAR(8),@StatDate,112)
	SET @UserTableName='MatrixLOG_log_WifiUser'+CONVERT(NVARCHAR(8),@StatDate,112)
	--赋值系统设定的设备出车次数时间间隔
	SELECT @DiffTime=[FieldValue]*60 FROM [MatrixMain].[dbo].[Matrix_dict_SysSetting] WHERE [FieldName]='MachineOutsetIntervalTime'

	SET @SQL=N'
		EXEC @TableExists=SYS_MatrixMain_TableExists @DatabaseName='''+@DatabaseName+''',@TableName='''+@RouteTableName+'''
		EXEC @TableExists2=SYS_MatrixMain_TableExists @DatabaseName='''+@DatabaseName+''',@TableName='''+@UserTableName+'''
		'
	EXEC SP_EXECUTESQL @SQL,N'@TableExists INT OUTPUT,@TableExists2 INT OUTPUT',@TableExists OUTPUT,@TableExists2 OUTPUT
	IF @TableExists<>1 AND @TableExists2<>1
	BEGIN
		RETURN 50001
	END
	CREATE TABLE #TEMP
	(
		[StatDate] INT NOT NULL,
		[MachineCode] [int] NOT NULL,
		[APTotalCount] [int] NULL,
		[APRepCount] [int] NULL,
		[PCTotalCount] [int] NULL,
		[PCRepCount] [int] NULL,
		[PubHotTotalCount] [int] NULL,
		[PubHotRepCount] [int] NULL,
		[G2TotalCount] [int] NULL,
		[G2RepCount] [int] NULL,
		[G3TotalCount] [int] NULL,
		[G3RepCount] [int] NULL,
		[OnlineTime] [int] NULL,
		[OnlineCount] [int] NULL,
		[StatTime] [datetime] NULL
	 )  

	CREATE TABLE #TempForMachineOnlineTime
	(
		RecNo INT IDENTITY,
		MachineCode nvarchar(10),
		CatchTime DATETIME,
		DiffHours INT
	)
	--将路由及用户数据插入#TempForMachineOnlineTime以便统计出车时长
	IF @TableExists=1 AND @TableExists2=1
	 SET @SQL=N'INSERT INTO #TempForMachineOnlineTime
				SELECT  MachineCode,
						CatchTime,
						0 AS DiffHours
						FROM ['+@DatabaseName+'].[dbo].['+@UserTableName+'] WITH(NOLOCK)
						GROUP BY MachineCode,CatchTime
				UNION 
				SELECT  MachineCode,
						CatchTime,
						0 AS DiffHours 
						FROM ['+@DatabaseName+'].[dbo].['+@RouteTableName+'] WITH(NOLOCK)
						GROUP BY MachineCode,CatchTime
						ORDER BY MachineCode,CatchTime
			'
	IF @TableExists=1 AND @TableExists2<>1
	 SET @SQL=N'INSERT INTO #TempForMachineOnlineTime
				SELECT  MachineCode,
						CatchTime,
						0 AS DiffHours 
						FROM ['+@DatabaseName+'].[dbo].['+@RouteTableName+'] WITH(NOLOCK)
						GROUP BY MachineCode,CatchTime
						ORDER BY MachineCode,CatchTime
			'
	IF @TableExists<>1 AND @TableExists2=1
	 SET @SQL=N'INSERT INTO #TempForMachineOnlineTime
				SELECT  MachineCode,
						CatchTime,
						0 AS DiffHours
						FROM ['+@DatabaseName+'].[dbo].['+@UserTableName+'] WITH(NOLOCK)
						GROUP BY MachineCode,CatchTime
						ORDER BY MachineCode,CatchTime
			'
	--PRINT (@SQL)
	EXEC(@SQL)

	UPDATE T2
	SET T2.DiffHours=DATEDIFF(SS,T1.CatchTime,T2.CatchTime)
	FROM #TempForMachineOnlineTime T2
	INNER JOIN #TempForMachineOnlineTime T1
	ON T2.MachineCode = T1.MachineCode
	AND T2.RecNo = T1.RecNo+1

	INSERT INTO #TEMP
	        ( StatDate ,
	          MachineCode ,
	          OnlineTime ,
	          OnlineCount ,
	          StatTime
	        )
	SELECT  CONVERT(NVARCHAR(8),@StatDate,112),
			MachineCode,
			(DATEDIFF(SS,MIN(CatchTime),MAX(CatchTime))
			-(SELECT SUM(CASE WHEN DiffHours>=@DiffTime THEN DiffHours ELSE 0 END) 
					FROM #TempForMachineOnlineTime 
					WHERE MachineCode=T.MachineCode)) AS OnlineTime,
			(SELECT SUM(CASE WHEN DiffHours>=@DiffTime THEN 1 ELSE 0 END)+1 
					FROM #TempForMachineOnlineTime 
					WHERE MachineCode=T.MachineCode) AS OnlineCount,
			GETDATE() AS StatTime
			FROM #TempForMachineOnlineTime T
			GROUP BY MachineCode
	
	IF @TableExists=1
	BEGIN
		SET @SQL=N'UPDATE T
					SET T.APRepCount = T2.APRepCount,
						T.APTotalCount = T2.APTotalCount,
						T.PubHotRepCount = T2.PubHotRepCount,
						T.PubHotTotalCount = T2.PubHotTotalCount
					FROM #TEMP T
						LEFT JOIN   
						(SELECT [MachineCode],
								COUNT(RouteMac) AS [APTotalCount],
								(COUNT(RouteMac)-COUNT(DISTINCT RouteMac)) AS [APRepCount],
								SUM(CASE WHEN SSID IN (''CHINANET'',''CHINAUNICOM'',''CMCC'') THEN 1 ELSE 0 END) AS [PubHotTotalCount],
								(SUM(CASE WHEN SSID IN (''CHINANET'',''CHINAUNICOM'',''CMCC'') THEN 1 ELSE 0 END)
								-COUNT(DISTINCT (CASE WHEN SSID IN (''CHINANET'',''CHINAUNICOM'',''CMCC'') THEN RouteMac ELSE NULL END))) AS [PubHotRepCount]
								FROM  '+@DatabaseName+'.[dbo].'+@RouteTableName+' WITH(NOLOCK)
								GROUP BY [MachineCode]) T2
					ON T.MachineCode=T2.[MachineCode]			
		'
		--PRINT @SQL
		EXEC(@SQL)
	END

	IF @TableExists2=1
	BEGIN
		SET @SQL=N'UPDATE T
					SET T.PCTotalCount = T2.PCTotalCount,
						T.PCRepCount = T2.PCRepCount
					FROM #TEMP T
						LEFT JOIN   
						(SELECT [MachineCode],
								COUNT(PcMac) AS [PCTotalCount],
								(COUNT(PcMac)-COUNT(DISTINCT PcMac)) AS [PCRepCount]
								FROM  '+@DatabaseName+'.[dbo].'+@UserTableName+' WITH(NOLOCK)
								GROUP BY [MachineCode]) T2
					ON T.MachineCode=T2.[MachineCode]				
		'
		--PRINT @SQL
		EXEC(@SQL)
	END
	  
	IF @Function=1
	 BEGIN
		 DELETE FROM [Matrix_stat_MachineOnline] WHERE [StatDate] = CONVERT(NVARCHAR(8),@StatDate,112)
		 INSERT INTO [Matrix_stat_MachineOnline]
					(
						[StatDate],
						[MachineCode] ,
						[APTotalCount],
						[APRepCount],
						[PCTotalCount],
						[PCRepCount],
						[PubHotTotalCount],
						[PubHotRepCount],
						[OnlineTime],
						[OnlineCount],
						[StatTime] 
					)
		SELECT 	[StatDate],
						[MachineCode] ,
						[APTotalCount],
						[APRepCount],
						[PCTotalCount],
						[PCRepCount],
						[PubHotTotalCount],
						[PubHotRepCount],
						[OnlineTime],
						[OnlineCount],
						[StatTime] 
		FROM #TEMP			
	 END
	IF @Function=2
			SELECT 	[StatDate],
					T.[MachineCode] ,
					m.MachineName ,
					[APTotalCount],
					[APRepCount],
					[PCTotalCount],
					[PCRepCount],
					[PubHotTotalCount],
					[PubHotRepCount],
					[OnlineTime],
					[OnlineCount],
					[StatTime] 
			FROM #TEMP t
			LEFT JOIN Matrix_tb_Machine m ON t.MachineCode = m.MachineCode
	
END