cwfx_accountdepta.txt

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

/*功能说明 给出参数：@ccode科目代码，
--@CY int,@CM1 int,@CM2 int 为当前时间段年月   @CmpY int,@CmpM1 int,@CmpM2 int为比较期时间段年月
结果输出该科目下各部门在分析期间及比较期间的科目分析方向的金额
*/
CREATE PROCEDURE dbo.cwfx_AccountDeptA @ccode varchar(15),@CY int,@CM1 int,@CM2 int,@CmpY int=0,@CmpM1 int=0,@CmpM2 int=0
AS
    declare @deptcode varchar(12),@Deptname nvarchar(20),@AccountType char(2),@Curr_V money,@Comp_V money
--@Curr_V money, 本期发生,@Comp_V money 比较期发生

BEGIN
if exists (select 1 from tempdb.dbo.sysobjects where name='##temp')
	drop table ##temp

create table ##temp(Deptcode varchar(12),Deptname nvarchar(20),Curr_V money,Comp_V money)
declare Analysis_Cursor cursor for
    Select distinct Deptcode From cwzz_AccSumAssi where ccode=@ccode and (year=@CY or year=@CmpY) order by deptcode
open Analysis_Cursor
fetch next from Analysis_Cursor into @deptcode
while  (@@FETCH_STATUS = 0)
begin

    select @Deptname=Deptname from gy_department where deptcode=@deptcode
    select @AccountType=BalanceOri from cwzz_AccCode where Ccode=@Ccode

    set @Curr_V=0
    set @Comp_V=0
    SELECT   @Curr_V=CASE @AccountType
	WHEN '借' THEN sum(mjje)
	WHEN '贷' THEN sum(mdje)
	 END  
	FROM cwzz_AccSumAssi
	WHERE ccode=@Ccode and DeptCode=@deptcode and year=@cy and period between @cm1 and @cm2
    
    SELECT   @Comp_V=CASE @AccountType
	WHEN '借' THEN sum(mjje)
	WHEN '贷' THEN sum(mdje)
	 END  
	FROM cwzz_AccSumAssi
	WHERE ccode=@Ccode and DeptCode=@deptcode and year=@CmpY and period between @CmpM1 and @CmpM2

    insert into ##temp values(@Deptcode,@DeptName,@Curr_V,@Comp_V)
    Fetch next from Analysis_Cursor into @deptcode
end
close Analysis_Cursor
deallocate  Analysis_Cursor
--select * from ##temp 
--drop table ##temp
END

GO