Use [Communityapp]go/** * * * object:storedprocedure [dbo]. [Sp_count_ownerpaylist] Script date:03/04/2016 11:32:31 * * * * **/SET ansi_nulls ongoset quoted_identifier Ongo--=============================================--Author: <author,,name>--Create Date: <create Date,,> ;--Description: <description,,>--=============================================ALTER PROCEDURE [dbo]. [Sp_count_ownerpaylist] @CommunityIDsint,--if all are isolated for 0, do not check the designated cell for 0 @FeeName nvarchar ( -), @StarTime asDateTime,--Start Time @EndTime asDateTime--end time as declare @iint, @jint, @months nvarchar ( -),--Current Month @FeeMoneydecimal(Ten,2), @CommunityIDint, @CommunityName nvarchar ( -) DECLARE @tCommunity TABLE--all the communities (Communityidint, CommunityName nvarchar ( -), Flagid TINYINT) DECLARE @tYuefen TABLE--all the months (months nvarchar ( -), Flagid TINYINT) DECLARE @tcount TABLE (communityname nvarchar ( -),--Community Months nvarchar ( -),--month Feemoneydecimal(Ten,2) --Payment amount) BEGIN--Get community Starif @CommunityIDs=0begin insert @tCommunitySelectCommunityid,communityname,0 fromCommunityBase.dbo.CommunityList EndElsebegin insert @tCommunitySelectCommunityid,communityname,0 fromCommunityBase.dbo.CommunityListwhereCommunityid=@CommunityIDs End--Get community End--Get month Star;with t as(Select@StarTime asy Union allSelectDATEADD (M,1, y) fromTwhereY <@EndTime) Insert @tYuefenSelectCONVERT (varchar (7), Y, -),0 fromT--Get month end SET @i=1While (@i>=1) ---first cycle star BEGINSet@CommunityID ="' Set@CommunityName ="'SELECT TOP1@CommunityID = Communityid, @CommunityName = CommunityName from @tCommunity WHERE flagid=0SET @i=@ @ROWCOUNT IF @i<=0GOTO Return_lab SET @j=1While (@j>=1) ---second Cycle star BEGIN SELECT TOP1@months = months from @tYuefen WHERE flagid=0SET @j=@ @ROWCOUNT IF @j<=0GOTO return_lab2 IF @FeeName="'begin SELECT @FeeMoney=sum (Feemoney) from ownerpaylist WHERE [email protected] and communityid= @CommunityID--month of Money endElsebegin SELECT @FeeMoney=sum (Feemoney) from ownerpaylist WHERE [email protected] and [email protected] and communityid= @CommunityID--month of Money endif@FeeMoney isNotNULLbegin Insert @tcount values (@CommunityName, @months, @FeeMoney) End IF @ @error=0UPDATE @tYuefen SET flagid=1WHERE months =@months return_lab2:end---second Loop End IF @ @error=0UPDATE @tCommunity SET flagid=1WHERE Communityid =@CommunityID return_lab:end---First Loop EndSelect* from@tcount
Month statistics in time period, query result loop