SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <xxxx> -- Create date: <2011-03-15> -- Description: <道具系统日报表统计> -- ============================================= ALTER PROCEDURE [dbo].[sp_PropsReport_DaySync] AS BEGIN --循环读取TbApps里的数据 --声明游标 DECLARE cursor_App CURSOR FOR SELECT Id FROM TbApps --打开游标 OPEN cursor_App DECLARE @AppId int DECLARE @ReportDay VARCHAR(10), @OrderCount INT, @ActAmt INT,@PersonCount INT SELECT @ReportDay=CONVERT(VARCHAR(10),DATEADD(dd, -1, GETDATE()),120) --统计TbPropsOrder里的Status=2的所有已扣费数据 FETCH NEXT FROM cursor_App INTO @AppId WHILE @@fetch_status=0 BEGIN SET @ActAmt=0 SET @OrderCount=0 SET @PersonCount=0 SELECT @ActAmt=ISNULL(SUM(ActPay),0), @OrderCount=COUNT(1),@PersonCount=COUNT(DISTINCT SndaId) FROM TbPropsOrder(NOLOCK) WHERE AppId=@AppId AND Status=2 AND LogTime>=@ReportDay AND LogTime<CONVERT(VARCHAR(10),GETDATE(),120) IF EXISTS(SELECT 1 FROM TbPropsOrderDayReport(NOLOCK) WHERE ReportDay=@ReportDay AND AppId=@AppId) UPDATE TbPropsOrderDayReport SET OrderCount=@OrderCount, ActAmt=@ActAmt ,PersonCount = @PersonCount WHERE ReportDay=@ReportDay AND AppId=@AppId ELSE INSERT TbPropsOrderDayReport(ReportDay, OrderCount, ActAmt, AppId,PersonCount) VALUES(@ReportDay, @OrderCount, @ActAmt, @AppId,@PersonCount) FETCH NEXT FROM cursor_App INTO @AppId END --关闭游标 CLOSE cursor_App --释放游标 DEALLOCATE cursor_App END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
没有整理与归纳的知识,一文不值!高度概括与梳理的知识,才是自己真正的知识与技能。 永远不要让自己的*、好奇、充满创造力的想法被现实的框架所束缚,让创造力*成长吧! 多花时间,关心他(她)人,正如别人所关心你的。理想的腾飞与实现,没有别人的支持与帮助,是万万不能的。
本文转自wenglabs博客园博客,原文链接:http://www.cnblogs.com/arxive/p/5959069.html,如需转载请自行联系原作者