bsidyPutOut.CustomerID AND dbo.T_Subsidymonthplan.[Month] = dbo.T_SubsidyPutOut.[Month] if (exists(select * FROM T_Subsidymonthplan WHERE (CustomerID=@id and [Month] = @month))) begin DELETE FROM T_Subsidymonthplan WHERE (CustomerID=@id and [Month] = @month) end else begin print ''''没有相关客户的本月补助冲突!或本月补助冲突已经清理完毕!'''' break end end /*清除冲突补助月份的T_SQL代码结束*/ set @month=''''2004-9-1'''' /*补助的月份,根据实际定*/ set @cardtype=4 /*卡的类别,根据实际定*/ 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 ''''更改当月的前期补助信息完成!'''' ------------------------------------------------------------------------------------------------- /*清除管理费的触发器及生成t_mngfarelog表的脚本*/ create trigger tr_mngfarelog on t_incomerec for insert,update as if exists(select * from t_incomerec where mngfare>0) begin declare @statid tinyint,@mealid tinyint,@port tinyint,@term tinyint, @sumdt datetime,@incomefare money,@mngfare money,@avginc money, @incomecount int select @statid=statid,@mealid=mealid,@port=port,@term=term, @sumdt=sumdt, @incomefare=incomefare,@incomecount=incomecount, @mngfare=mngfare from t_incomerec where mngfare>0 update t_incomerec set mngfare=0 where statid=@statid and mealid=@mealid and port=@port and term=@term and sumdt=@sumdt set @avginc=@incomefare/@incomecount if @avginc>5/*平均消费值,根据实际定*/ begin update t_incomerec set incomefare=4.5*incomecount where statid=@statid and mealid=@mealid and port=@port and term=@term and sumdt=@sumdt end insert into t_mngfarelog values(@statid,@mealid,@port,@term, @sumdt,@incomefare,@incomecount,@mngfare) end go if exists (select * from dbo.sysobjects where id = object_id(N''''[dbo].[t_mngfarelog]'''') and OBJECTPROPERTY(id, N''''IsUserTable'''') = 1) /*生成t_mngfarelog表的脚本*/ drop table [dbo].[t_mngfarelog] GO CREATE TABLE [dbo].[t_mngfarelog] ( [statid] [tinyint] NOT NULL , [mealid] [tinyint] NOT NULL , [port] [tinyint] NOT NULL , [term] [tinyint] NOT NULL , [sumdt] [datetime] NOT NULL , [incomefare] [money] NOT NULL , [incomecount] [int] NOT NULL , [mngfare] [money] NULL ) ON [PRIMARY] GO
上一页 [1] [2] [3] [4] [办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE [办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全 [办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法 [办公软件]Linux程序员必读:中文化与GB18030标准 [办公软件]linux指令大全 [办公软件]制作Linux启动盘的四种方法 [办公软件]Linux文件系统的反删除方法
|