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
