USE [CRM01]GO/****** Object:  StoredProcedure [dbo].[Account3YearsTrade]    Script Date: 07/15/2015 08:34:37 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- Batch submitted through debugger: SQLQuery1.sql|7|0|C:\Users\crmqas2\AppData\Local\Temp\3\~vs3E13.sqlALTER PROCEDURE  [dbo].[Account3YearsTrade] as DECLARE @AccountId UNIQUEIDENTIFIER ,    @yt_total_price money ,    @OpportunityId UNIQUEIDENTIFIER ,    @yt_invoice_date int ,    @yt_lastyeartrading money ,    @yt_toptwoannualturnover money ,    @yt_topthreeannualturnover money ,    @nowdate int     --声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同  DECLARE mycursor CURSORFOR    select accountid from accountBase WHERE StateCode=0     SELECT @nowdate=DATEPART(year,Getdate())--打开游标  OPEN mycursor          --从游标里取出数据赋值到我们刚才声明的2个变量中  FETCH NEXT FROM mycursor INTO @AccountId    --判断游标的状态      -- 0 fetch语句成功          ---1 fetch语句失败或此行不在结果集中          ---2 被提取的行不存在  	WHILE ( @@fetch_status = 0 ) 		BEGIN 			set @yt_lastyeartrading = 0			set @yt_toptwoannualturnover = 0			set @yt_topthreeannualturnover = 0			DECLARE mycursortwo CURSOR			FOR   			  WITH childAccount(accountid,ParentAccountId) as 			   ( 				SELECT accountid,ParentAccountId FROM AccountBase WHERE accountid=@AccountId				UNION ALL 				SELECT A.accountid,A.ParentAccountId FROM AccountBase A,childAccount b 				where a.ParentAccountId = b.accountid 			   )				select yt_total_price,DATEPART(year,yt_invoice_date),OpportunityId from Opportunity				where yt_arrive in(SELECT accountid  from childAccount)   and yt_status=100000002			    open mycursortwo    				FETCH NEXT FROM mycursortwo INTO @yt_total_price,@yt_invoice_date,@OpportunityId				while( @@fetch_status = 0 )					begin						if(@yt_invoice_date=@nowdate-1)							begin								set @yt_lastyeartrading = @yt_lastyeartrading + isnull(@yt_total_price,0.0000)							end						else if(@yt_invoice_date=@nowdate-2)							begin								set @yt_toptwoannualturnover = @yt_toptwoannualturnover + isnull(@yt_total_price,0.0000)							end						else if(@yt_invoice_date=@nowdate-3)							begin								set @yt_topthreeannualturnover = @yt_topthreeannualturnover + isnull(@yt_total_price,0.0000)							end				FETCH NEXT FROM mycursortwo  INTO @yt_total_price,@yt_invoice_date,@OpportunityId					end		   CLOSE mycursortwo		   DEALLOCATE mycursortwo		   update Account		   set yt_lastyeartrading = @yt_lastyeartrading,yt_lastyeartrading_Base = @yt_lastyeartrading,yt_toptwoannualturnover = @yt_toptwoannualturnover,			   yt_toptwoannualturnover_Base = @yt_toptwoannualturnover,yt_topthreeannualturnover = @yt_topthreeannualturnover,yt_topthreeannualturnover_Base = @yt_topthreeannualturnover		   where  AccountId = @AccountId		   		   --用游标去取下一条客户  		   FETCH NEXT FROM mycursor  INTO @AccountId		END      --关闭游标  CLOSE mycursor      --撤销游标  DEALLOCATE mycursor