CREATEPROCEDURE SelectUserCurData @user_fzint AS declare@sqlnvarchar(4000) declare@yearint set@year=year(getdate()) set@user_fz=11 ifobject_id('tempdb..#tempUserTemple') isnotnull droptable #tempUserTemple CREATETABLE #tempUserTemple( [title][int]null, [House_Id][int]NULL , [User_Yj][float]null, [User_Sj][float]null, [User_Qf][float]null ) while (@year>2006) begin set@sql=' if exists(select * from sysobjects where id = OBJECT_ID(''[HouseTemple'+cast(@yearasnvarchar(4))+']'') and OBJECTPROPERTY(id, ''IsUserTable'') = 1) begin declare @tempyj float; declare @tempsj float; SELECT @tempyj=sum([House_Mj]*[House_Price]*[temple_month]) FROM HouseTemple'+cast(@yearasnvarchar(4))+' where House_Id=1 select @tempsj=isnull(sum(invalue),0) from InDataList'+cast(@yearasnvarchar(4))+' where House_Id=1 Insert into #tempUserTemple([title],[House_Id],[User_Yj],[User_Sj],[User_Qf]) SELECT 2008, '+cast(@user_fzasnvarchar(4))+', @tempyj, @tempsj, @tempyj-@tempsj FROM UserTemple'+cast(@yearasnvarchar(4))+' where User_Fz='+cast(@user_fzasnvarchar(4))+' end ' print@sql exec (@sql) set@year=@year-1; end select*from ( select*from #tempUserTemple union select title ,House_Id ,totle as User_Yj,yj as User_Sj ,(totle-yj) as User_Qf from ( SELECT Cur_Year as title ,[House_Id],[Totle],yj=isnull((selectsum(InValue) from OldDataList where Cur_User_Id=OldDataTemple.House_Id and InYear=OldDataTemple.Cur_Year),0) FROM OldDataTemple where House_Id=@user_fz )d )b orderby title desc COMPUTESUM(User_Yj), sum(User_Sj), sum(User_Qf) GO
posted on
2008-05-10 16:40gwazy
阅读(789)
评论(0)
编辑收藏举报