|
|
 |
专题栏目 |
 |
|
相关文章 |
|
|
|
|
|
 |
公安补助的卡实现在窗机领取的T-SQL处理代码 |
热 |
|
| 公安补助的卡实现在窗机领取的T-SQL处理代码 |
|
作者：cyz1980 文章来源：不详 点击数： 更新时间：2007-11-14 13:07:07  |
|
|
/*公安专几个月或一年只有一次补助的卡的处理代码,当月补助形成后发放前处理的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
''''更改当月的前期补助信息完成!''''
|
|
| 文章录入：mintao 责任编辑：mintao |
|
|
上一篇文章： 怎样做sql server数据库的还原
下一篇文章： 福建公安专科学校贫困生补助处理T-SQl代码 |
|
|
| 【字体：小 大】【发表评论】【加入收藏】【告诉好友】【打印此文】【关闭窗口】 |
|
 注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] |
| 网友评论：（只显示最新10条。评论内容只代表网友观点，与本站立场无关！） |
| |
|
|
|
|
