公安專幾個月或一年只有一次補助的卡實現在窗機領取的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.kjueaiud.com