---------执行前最好能做下数据备份
------到sql查询分析器中选择赢通帐套数据库名称执行下面全部语句。(一次执行)
--总部重整会员余额;
declare @branch_tmp varchar(100),@vip_no varchar(50),@save_amt numeric(18,2)
declare cur_vip cursor for
select vip_no from t_vip_info
open cur_vip
fetch cur_vip into @vip_no
while @@fetch_status =0
begin
--重算存储余额;
delete t_vip_money where vip_no = @vip_no and money_type ='4' and branch_no ='*'
select @save_amt = IsNull(sum(save_amt),0.00) from t_vip_flow where vip_no =@vip_no
if @save_amt <>0.00
insert t_vip_money(vip_no, branch_no, money_type, vip_sort, value_amt, use_amt, memo)
values (@vip_no,'*','4',1,@save_amt,0.00,'重整数据')
--重算赠送额;
delete t_vip_money where vip_no = @vip_no and money_type ='2' and branch_no ='*'
select @save_amt = IsNull(sum(give_amt),0.00) from t_vip_flow where vip_no =@vip_no
if @save_amt <>0.00
insert t_vip_money(vip_no, branch_no, money_type, vip_sort, value_amt, use_amt, memo)
values (@vip_no,'*','2',1,@save_amt,0.00,'重整数据')
--重算积分;
delete t_vip_money where vip_no = @vip_no and money_type ='5' and branch_no ='*'
select @save_amt = IsNull(sum(integral),0.00) from t_vip_flow where vip_no =@vip_no
if @save_amt>0.00
insert t_vip_money(vip_no, branch_no, money_type, vip_sort, value_amt, use_amt, memo)
values (@vip_no,'*','5',1,@save_amt,0.00,'重整数据')
--重算返利
delete t_vip_money where vip_no = @vip_no and money_type ='1' and branch_no ='*'
select @save_amt = IsNull(sum(ret_amt),0.00) from t_vip_flow where vip_no =@vip_no
if @save_amt>0.00
insert t_vip_money(vip_no, branch_no, money_type, vip_sort, value_amt, use_amt, memo)
values (@vip_no,'*','1',1,@save_amt,0.00,'重整数据')
--重算零钱余额
delete t_vip_money where vip_no = @vip_no and money_type ='3' and branch_no ='*'
select @save_amt = IsNull(sum(zero_amt),0.00) from t_vip_flow where vip_no =@vip_no
if @save_amt>0.00
insert t_vip_money(vip_no, branch_no, money_type, vip_sort, value_amt, use_amt, memo)
values (@vip_no,'*','3',1,@save_amt,0.00,'重整数据')
fetch cur_vip into @vip_no
end
deallocate cur_vip
【官方】会员重整、会员积分重整、储值重整、还利重整、零钱重整、重算赠送?
- 提问者: ytkf007
- 等级:问不倒翁
- 时间:2014-04-15 09:41
- 悬赏:0
- 解决时间:2014-04-18 16:59
最佳答案
学习了
- 回答者:xiaobird
- 等级:钻石会员
- 时间:2014-04-15 15:41
- 提问者对最佳回答的评论:
- go
---重算积分最好用此语句。
go
declare @branch_tmp varchar(100),@vip_no varchar(50),@save_amt numeric(18,2)
declare cur_vip cursor for
select vip_no from t_vip_info where vip_dispno='093'
open cur_vip
fetch cur_vip into @vip_no
while @@fetch_status =0
begin
--重算积分;
delete t_vip_money where vip_no = @vip_no and money_type ='5' and branch_no ='*'
select @save_amt = IsNull(sum(integral),0.00) from t_vip_flow where vip_no =@vip_no
if @save_amt>0.00
insert t_vip_money(vip_no, branch_no, money_type, vip_sort, value_amt, use_amt, memo,voucher_no)
values (@vip_no,'*','5',1,@save_amt,0.00,'重整数据',LEFT(CONVERT(VARCHAR,GETDATE(),112),6))
fetch cur_vip into @vip_no
end
deallocate cur_vip
对最佳答案的评论 共 1 条
--总部重整会员余额;
go
declare @branch_tmp varchar(100),@vip_no varchar(50),@save_amt numeric(18,2)
declare cur_vip cursor for
select vip_no from t_vip_info
open cur_vip
fetch cur_vip into @vip_no
while @@fetch_status =0
begin
--重算积分;
delete t_vip_money where vip_no = @vip_no and money_type ='5' and branch_no ='*'
select @save_amt = IsNull(sum(integral),0.00) from t_vip_flow where vip_no =@vip_no
if @save_amt>0.00
insert t_vip_money(vip_no, branch_no, money_type, vip_sort, value_amt, use_amt, memo,voucher_no)
values (@vip_no,'*','5',1,@save_amt,0.00,'重整数据',LEFT(CONVERT(VARCHAR,GETDATE(),112),6))
fetch cur_vip into @vip_no
end
deallocate cur_vip
go
declare @branch_tmp varchar(100),@vip_no varchar(50),@save_amt numeric(18,2)
declare cur_vip cursor for
select vip_no from t_vip_info
open cur_vip
fetch cur_vip into @vip_no
while @@fetch_status =0
begin
--重算积分;
delete t_vip_money where vip_no = @vip_no and money_type ='5' and branch_no ='*'
select @save_amt = IsNull(sum(integral),0.00) from t_vip_flow where vip_no =@vip_no
if @save_amt>0.00
insert t_vip_money(vip_no, branch_no, money_type, vip_sort, value_amt, use_amt, memo,voucher_no)
values (@vip_no,'*','5',1,@save_amt,0.00,'重整数据',LEFT(CONVERT(VARCHAR,GETDATE(),112),6))
fetch cur_vip into @vip_no
end
deallocate cur_vip
- 评论者:ytkf007
其它回答 共 3 条
学习
- 回答者:kh359
- 等级:问不倒翁
- 时间:2014-04-15 10:12
收藏了
- 回答者:bonchen
- 等级:问不倒翁
- 时间:2014-04-15 11:16
这个要学习要学习、
- 回答者:zhaoshuai888
- 等级:问不倒翁
- 时间:2014-04-18 11:11
快到期问题