cwfx_casha.txt

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

CREATE PROCEDURE dbo.cwfx_CashA @date1 datetime,@date2 datetime
AS
    declare @ccode varchar(15),@Cname varchar(40),@Direction bit,@V 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),Direction bit,V money,YearV money,YearVB money)
declare Budget_Cursor cursor for
    Select Direction,CCode From cwfx_CashIncomeCostSet where year=year(@date1) order by direction,ccode
open Budget_Cursor
fetch next from Budget_Cursor into @Direction,@ccode
while  (@@FETCH_STATUS = 0)
begin

    select @Cname=Cname from cwzz_Acccode where ccode=@CCode
    set @V=0
    set @YearV=0
	if @Direction=0                --0表示现金流入
	    select @V=sum(jfje) from cwzz_AccVouch where ccode=@ccode and VouchId in (select VouchId from Cwzz_AccVouchMain where DDate between @date1 and @date2 and bookflag=1)
	else
	    select @V=sum(dfje) from cwzz_AccVouch where ccode=@ccode and VouchId in (select VouchId from Cwzz_AccVouchMain where DDate between @date1 and @date2 and bookflag=1)

	if @Direction=0
		select @YearV=sum(mjje) from cwzz_AccSum where ccode=@ccode and year=year(@date1) 
	else
		select @YearV=sum(mdje) from cwzz_AccSum where ccode=@ccode and year=year(@date1) 
    set @YearBV=0		
    SELECT   @YearBV=Budget
		FROM cwfx_CashIncomeCostSet
		where year=year(@date1) and ccode=@ccode and direction=@Direction
	
	insert into ##temp values(@Ccode,@Cname,@Direction,@V,@YearV, @YearBV)
	fetch next from Budget_Cursor into @Direction,@ccode
end
close Budget_Cursor
deallocate  Budget_Cursor
select * from ##temp
--drop table ##temp
END
GO