USE [SSIS_ExtractData] GO /****** Object: StoredProcedure [dbo].[sp_AntifakeAnalysis] Script Date: 05/05/2017 16:01:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Yaojl -- Create date: <Create Date,,2017-03-16> -- Description: <Description,,扫码首次多次分析> -- ============================================= ALTER PROCEDURE [dbo].[sp_AntifakeAnalysis] AS BEGIN BEGIN TRY BEGIN TRAN IF EXISTS ( SELECT ID FROM dbo.Rep_AntifakeAnalysis ) BEGIN TRUNCATE TABLE dbo.Rep_AntifakeAnalysis END --往前推6个月 DECLARE @temp DATETIME = CONVERT(DATETIME, CONVERT(VARCHAR(7), DATEADD(MONTH, -5, GETDATE()), 120) + '-01'); DECLARE @first INT; DECLARE @repeat INT; DECLARE @total INT; WHILE @temp <=getdate() BEGIN ---全行业 SELECT @first = COUNT(FFAntiFakeCode) FROM CRM_AntiFakeQRec WHERE FSystime >= @temp AND FSystime < DATEADD(MONTH, 1, @temp) AND FsearchNum = 1 SELECT @repeat = COUNT(FFAntiFakeCode) FROM CRM_AntiFakeQRec WHERE FSystime >= @temp AND FSystime < DATEADD(MONTH, 1, @temp) AND FsearchNum > 1 SET @total = @first + @repeat; INSERT INTO dbo.Rep_AntifakeAnalysis ( DataNum , FirstNum , RepeatNum , TotalNum ) VALUES ( --CONVERT(varchar(7), @temp, 111) , -- DataNum - nvarchar(50) Datename(month,@temp)+'月', @first , -- FirstNum - int @repeat , -- RepearNum - int @total -- TotalNum - int ) ------分行业 -- --创建临时表 -- DECLARE @tradeID INT; --if not object_id('Tempdb..#A') is null -- drop table #A --Create table #A([TradeID] nvarchar(100)) --Insert #A SELECT TradeID FROM dbo.CRM_AntiFakeQRec GROUP BY TradeID --WHILE EXISTS(SELECT TradeID FROM #A) --BEGIN --SET ROWCOUNT 1 --SELECT @tradeID=TradeID FROM #A --SET ROWCOUNT 0 --DELETE FROM #A WHERE TradeID=@tradeID --PRINT @tradeID -- SELECT @first = COUNT(FFAntiFakeCode) -- FROM CRM_AntiFakeQRec -- WHERE FSystime >= @temp -- AND FSystime < DATEADD(MONTH, 1, @temp) -- AND FsearchNum = 1 AND TradeID=@tradeID -- SELECT @repeat = COUNT(FFAntiFakeCode) -- FROM CRM_AntiFakeQRec -- WHERE FSystime >= @temp -- AND FSystime < DATEADD(MONTH, 1, @temp) -- AND FsearchNum > 1 AND TradeID=@tradeID -- SET @total = @first + @repeat; -- INSERT INTO dbo.Rep_AntifakeAnalysis -- ( DataNum , -- FirstNum , -- RepeatNum , -- TotalNum, -- TradeID -- ) -- VALUES ( CONVERT(varchar(7), @temp, 111) , -- DataNum - nvarchar(50) -- @first , -- FirstNum - int -- @repeat , -- RepearNum - int -- @total, -- TotalNum - int -- @tradeID -- ) -- END SET @temp = DATEADD(MONTH, 1, @temp) END COMMIT TRAN END TRY BEGIN CATCH IF XACT_STATE() = -1 BEGIN ROLLBACK TRAN; END PRINT '更新失败'; END CATCH END