循环年所有的月分统计

USE [CommunityApp]
GO
/****** Object:  StoredProcedure [dbo].[sp_count_OwnerInfo]    Script Date: 03/04/2016 11:34:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_count_OwnerInfo]  
 @CommunityIDs int--如果为0查出所有的，不为0则查指定小区
AS
  declare @i int,
    @j int,
   @Allyear int,--年数
   @year  varchar(30),--年标
  @CommunityID int,
  @CommunityName nvarchar(50),
  @yearCount  int  ,
  @month1Count  int  ,
  @month2Count  int  ,
  @month3Count  int  ,
  @month4Count  int  ,
  @month5Count  int  ,
  @month6Count  int  ,
  @month7Count  int  ,
  @month8Count  int  ,
  @month9Count  int  ,
  @month10Count  int  ,
  @month11Count  int  ,
  @month12Count  int 
  
 DECLARE @tCommunity TABLE  
 (  
  CommunityID  int  ,
  CommunityName nvarchar(50)
  ,FlagID   TINYINT    
 ) 
 
 DECLARE @tcount TABLE  
 (  
  CommunityName nvarchar(50),
  years   int,
  yearCount  int  ,
  month1Count  int  ,
  month2Count  int  ,
  month3Count  int  ,
  month4Count  int  ,
  month5Count  int  ,
  month6Count  int  ,
  month7Count  int  ,
  month8Count  int  ,
  month9Count  int  ,
  month10Count  int  ,
  month11Count  int  ,
  month12Count  int 
 ) 
 
BEGIN  

IF @CommunityIDs=0
begin
 insert @tCommunity  select CommunityID,CommunityName,0 from CommunityBase.dbo.CommunityList
 end
 else
 begin
  insert @tCommunity  select CommunityID,CommunityName,0 from CommunityBase.dbo.CommunityList where CommunityID=@CommunityIDs
 end

 
 SET @i=1  
 WHILE( @i>=1)  
 BEGIN  
    
  set @CommunityID=‘‘  
   set @CommunityName=‘‘  
  SELECT TOP 1 @CommunityID = CommunityID,@CommunityName = CommunityName FROM @tCommunity  WHERE flagID=0  
  SET @i=@@ROWCOUNT  
    
  IF @i<=0 GOTO Return_Lab  
     
 set @Allyear=year(getdate())-2015---第二次循环star
 
 SET @j= 0
 WHILE( @j<=@Allyear)  
  BEGIN
      SET @year=2015+@j
      
    SELECT @yearCount=count(*) FROM OwnerInfo WHERE datediff(year,CreateTime,@year+‘-1-1‘)=0 and CommunityID= @CommunityID  --年
    SELECT @month1Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+‘-1-1‘)=0 and CommunityID= @CommunityID  --1月
    SELECT @month2Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+‘-2-1‘)=0 and CommunityID= @CommunityID  --2月
    SELECT @month3Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+‘-3-1‘)=0 and CommunityID= @CommunityID  --3月
    SELECT @month4Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+‘-4-1‘)=0 and CommunityID= @CommunityID  --4月
    SELECT @month5Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+‘-5-1‘)=0 and CommunityID= @CommunityID  --5月
    SELECT @month6Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+‘-6-1‘)=0 and CommunityID= @CommunityID  --6月
    SELECT @month7Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+‘-7-1‘)=0 and CommunityID= @CommunityID  --7月
    SELECT @month8Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+‘-8-1‘)=0 and CommunityID= @CommunityID  --8月
    SELECT @month9Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+‘-9-1‘)=0  and CommunityID= @CommunityID --9月
    SELECT @month10Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+‘-10-1‘)=0 and CommunityID= @CommunityID  --10月
    SELECT @month11Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+‘-11-1‘)=0 and CommunityID= @CommunityID  --11月
    SELECT @month12Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+‘-12-1‘)=0 and CommunityID= @CommunityID  --12月

   insert @tcount values(@CommunityName,@year,@yearCount,@month1Count,@month2Count,@month3Count,@month4Count,@month5Count,@month6Count,@month7Count,@month8Count,@month9Count,@month10Count,@month11Count,@month12Count) 
   SET @j+=1
   end ---第二次循环 end
   
   IF @@error=0   
      UPDATE @tCommunity SET flagID=1 WHERE CommunityID = @CommunityID
       
 Return_Lab:  
 END 
 select * from   @tcount
End