公安专几个月或一年只有一次补助的卡实现在窗机领取的T-SQL处理代码

/*公安专几个月或一年只有一次补助的卡的处理代码,当月补助形成后发放前处理的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 '更改当月的前期补助信息完成!'

原文转自：http://www.ltesting.net