/*公安专几个月或一年只有一次补助的卡的处理代码,当月补助形成后发放前处理的T_SQL代码(除该类卡形成补助当月外) */ declare
@month/*月份*/ datetime,@k /*卡类别*/ int,@customerid int /*客户号*/,
@subsidyfare/*补助额*/ money set @k=6 /*卡类别，根据实际定*/ select @month= month from
t_subsidymonthplan/*获取补助当月份值*/ declare custid_dist cursor for select distinct
customerid from t_subsidynotputout where cardtype=@k open custid_dist fetch next
from custid_dist into @customerid while (@@fetch_status=0) begin insert into
t_subsidymonthplan(month,customerid,cardtype,subsidy)
values(@month,@customerid,@k,0) fetch next from custid_dist into @customerid end
close custid_dist deallocate custid_dist declare custid_subsidyfare cursor for
select customerid,sum(subsidy) as sum_subsidy from t_subsidynotputout where
cardtype=@k group by customerid open custid_subsidyfare fetch next from
custid_subsidyfare into @customerid,@subsidyfare while (@@fetch_status=0) begin
update t_customers set subsidydt=@month
,cursubsidyfare=@subsidyfare,subsidyout=''''F'''' where customerid =@customerid
fetch next from custid_subsidyfare into @customerid,@subsidyfare end close
custid_subsidyfare deallocate custid_subsidyfare
/*公安专补助月报表信息纠正T_SQL代码(当月补助发放完后)*/ declare @k int, @k1 int,@k2 int,@month
datetime,@subsidyFare money,@cardtype tinyint,@subsidyFare1 money,@subsidyFare2
money select @month=month from t_subsidymonthplan /*获取当月补助的月份*/ set @cardtype=4
/*卡的类别(除5、6类卡)，根据实际定*/ delete from T_subsidymonthplan where cardtype in
(5,6)/*注意卡类*/ delete from t_subsidynotputout where customerid in (select
customerid from t_customers where subsidyout=''''T'''' and cardtype in
(5,6))/*注意卡类*/ select @k=count(*),@subsidyFare=sum(subsidy) from t_subsidypre
where cardtype=@cardtype and month=@month /*统计当月补助计划人数及金额，以t_subsidypre帐本为准*/ if
exists(select * from t_subsidymonth where plancount=@k and
plansubsidy=@subsidyFare and month=@month and cardtype=@cardtype)
/*判断当月补助计划数及金额是否正确*/ begin select @k1=count(*),@subsidyFare1=sum(subsidy) from
t_subsidymonthplan where cardtype=@cardtype and month=@month /*统计当月补助未发人数及金额*/
if @subsidyFare1 is null begin set @subsidyFare1=0 end set @k2=@k-@k1 set
@subsidyFare2=@subsidyFare-@subsidyFare1 update t_subsidymonth set
putoutcount=@k2,putoutsubsidy=@subsidyFare2 where cardtype=@cardtype and
month=@month /*当月已发人数及金额=当月补助计划人数及金额-当月补助未发人数及金额*/ print ''''更改当月的补助信息完成!''''
end else begin print ''''计划总数不一致!'''' end select
@k=count(*),@subsidyFare=sum(subsidy) from t_subsidynotputout where
cardtype=@cardtype and month<@month /*统计前期补助未发人数及金额*/ select
@k1=planprecount,@subsidyFare1=planpre from t_subsidymonth where
cardtype=@cardtype and month=@month /*统计当月前期补助计划人数及金额*/ set @k2=@k1-@k set
@subsidyFare2=@subsidyFare1-@subsidyFare if @subsidyFare2 is null begin set
@subsidyFare2=0 end update t_subsidymonth set
putoutprecount=@k2,putoutpre=@subsidyFare2 where cardtype=@cardtype and
month=@month /*当月前期已发人数及金额=当月前期补助计划人数及金额-前期补助未发人数及金额*/ print
''''更改当月的前期补助信息完成!''''
[办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE [办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全 [办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法 [办公软件]Linux程序员必读：中文化与GB18030标准 [办公软件]linux指令大全 [办公软件]制作Linux启动盘的四种方法 [办公软件]Linux文件系统的反删除方法
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
