数据库

本类阅读TOP10

·SQL语句导入导出大全
·SQL Server日期计算
·SQL语句导入导出大全
·SQL to Excel 的应用
·Oracle中password file的作用及说明
·MS SQLServer OLEDB分布式事务无法启动的一般解决方案
·sqlserver2000数据库置疑的解决方法
·一个比较实用的大数据量分页存储过程
·如何在正运行 SQL Server 7.0 的服务器之间传输登录和密码
·SQL中两台服务器间使用连接服务器

分类导航
VC语言Delphi
VB语言ASP
PerlJava
Script数据库
其他语言游戏开发
文件格式网站制作
软件工程.NET开发
公安专几个月或一年只有一次补助的卡实现在窗机领取的T-SQL处理代码

作者:未知 来源:月光软件站 加入时间:2005-6-5 月光软件站

/*公安专几个月或一年只有一次补助的卡的处理代码,当月补助形成后发放前处理的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 [email protected] 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 [email protected] group by customerid open custid_subsidyfare fetch next from custid_subsidyfare into @customerid,@subsidyfare while (@@fetch_status=0) begin update t_customers set [email protected] ,[email protected],subsidyout='F' where customerid [email protected] 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 [email protected] and [email protected] /*统计当月补助计划人数及金额,以t_subsidypre帐本为准*/ if exists(select * from t_subsidymonth where [email protected] and [email protected] and [email protected] and [email protected]) /*判断当月补助计划数及金额是否正确*/ begin select @k1=count(*),@subsidyFare1=sum(subsidy) from t_subsidymonthplan where [email protected] and [email protected] /*统计当月补助未发人数及金额*/ if @subsidyFare1 is null begin set @subsidyFare1=0 end set @k2=@k-@k1 set @subsidyFare2=@subsidyFare-@subsidyFare1 update t_subsidymonth set [email protected],[email protected] where [email protected] and [email protected] /*当月已发人数及金额=当月补助计划人数及金额-当月补助未发人数及金额*/ print '更改当月的补助信息完成!' end else begin print '计划总数不一致!' end select @k=count(*),@subsidyFare=sum(subsidy) from t_subsidynotputout where [email protected] and month<@month /*统计前期补助未发人数及金额*/ select @k1=planprecount,@subsidyFare1=planpre from t_subsidymonth where [email protected] and [email protected] /*统计当月前期补助计划人数及金额*/ set @k2=@k1-@k set @subsidyFare2=@subsidyFare1-@subsidyFare if @subsidyFare2 is null begin set @subsidyFare2=0 end update t_subsidymonth set [email protected],[email protected] where [email protected] and [email protected] /*当月前期已发人数及金额=当月前期补助计划人数及金额-前期补助未发人数及金额*/ print '更改当月的前期补助信息完成!'


相关文章

相关软件




月光软件源码下载编程文档电脑教程网站优化网址导航网络文学游戏天地生活休闲写作范文安妮宝贝站内搜索
电脑技术编程开发网络专区谈天说地情感世界游戏元素分类游戏热门游戏体育运动手机专区业余爱好影视沙龙
音乐天地数码广场教育园地科学大观古今纵横谈股论金人文艺术医学保健动漫图酷二手专区地方风情各行各业

月光软件站·版权所有