cwfx_costbudget.txt

财务分析 财财务分析务分析

CREATE PROCEDURE dbo.cwfx_CostBudget @CY int,@CM1 int,@CM2 int,@CmpY int=0,@CmpM1 int=0,@CmpM2 int=0
AS
    declare @Flag char(2),@t1 money,@i integer,@b1 money,@ccode varchar(15),@Cname varchar(40),@V money,@CmpV money,@B money,@YearV money,@YearBV money
BEGIN
if exists (select 1 from tempdb.dbo.sysobjects where name='##temp')
	drop table ##temp

create table ##temp(Ccode varchar(15),Cname varchar(40),V money,CmpV money,B money,YearV money,YearVB money)
declare Budget_Cursor cursor for
    Select CCode From cwfx_BaseProjSet where Account='成本' order by ccode
open Budget_Cursor
fetch next from Budget_Cursor into @ccode
while  (@@FETCH_STATUS = 0)
begin

    select @Flag=balanceOri,@Cname=Cname from cwzz_Acccode where ccode=@CCode
    set @V=0
    set @YearV=0
	if @Flag='借'
	    select @V=sum(mjje) from cwzz_AccSum where ccode=@ccode and year=@cy and period between @cm1 and @cm2
	else
	    select @V=sum(mdje) from cwzz_AccSum where ccode=@ccode and year=@cy and period between @cm1 and @cm2

	if @Flag='借'
		select @YearV=sum(mjje) from cwzz_AccSum where ccode=@ccode and year=@cy
	else
		select @YearV=sum(mdje) from cwzz_AccSum where ccode=@ccode and year=@cy
		
		
	set @b=0
	set @t1=0
	set @i=@cm1
	while(@i<=@cm2)
	begin
		SELECT   @t1=CASE @i
	        WHEN 1 THEN budget_1+Add_1
		WHEN 2 THEN budget_2+Add_2
		WHEN 3 THEN budget_3+Add_3
		WHEN 4 THEN budget_4+Add_4
		WHEN 5 THEN budget_5+Add_5
		WHEN 6 THEN budget_6+Add_6
		WHEN 7 THEN budget_7+Add_7
		WHEN 8 THEN budget_8+Add_8
		WHEN 9 THEN budget_9+Add_9
		WHEN 10 THEN budget_10+Add_10
		WHEN 11 THEN budget_11+Add_11
		WHEN 12 THEN budget_12+Add_12
		 END  
		FROM cwfx_AccountBudgetMaster
		WHERE year=@cy and ccode=@ccode
	set @b=@b+@t1
	set @i=@i+1
	end
    set @YearBV=0	
    SELECT   @YearBV=budget_1+Add_1+budget_2+Add_2+budget_3+Add_3+budget_4+Add_4+budget_5+Add_5+budget_6+Add_6+budget_7+Add_7+budget_8+Add_8+budget_9+Add_9+budget_10+Add_10+budget_11+Add_11+budget_12+Add_12
		FROM cwfx_AccountBudgetMaster
		where year=@cy and ccode=@ccode
	
	set @CmpV=0
	if (@CmpY <>0 and  @CmpM1 <>0 and @CmpM2<>0)
	begin
  	    if @Flag='借'
    		select @CmpV=sum(mjje) from cwzz_AccSum where ccode=@ccode and year=@CmpY and period between @CmpM1 and @CmpM2
	    else
		select @CmpV=sum(mdje) from cwzz_AccSum where ccode=@ccode and year=@CmpY and period between @CmpM1 and @CmpM2
	end

	insert into ##temp values(@Ccode,@Cname,@V, @CmpV, @B, @YearV, @YearBV)
	fetch next from Budget_Cursor into @ccode
end
close Budget_Cursor
deallocate  Budget_Cursor
--select * from ##temp
--drop table ##temp
END
GO