SQL 临时表 执行每日任务 会员表积分

需要返积分的会员临时表

declare @member table (
  id varchar(50),		   --用户ID
  TimesMoney decimal(18,2),--每日付款额度
  ReadyMoney decimal(18,2)--已返利
)

insert into @member 
select M.Id ,U.BackBV , SUM(L.NomIn) as ReadyMoney from MemberAccount as M , UserStype as U ,ListPvorSv as L where M.DataState=1 and M.UserStype =U.Id and M.Id = L.MemberAccountId
group by M.Id,U.BackBV

select * from @member

 

--更新会员账户
update MemberAccount set PV=PV+h.TimesMoney from @member as h where MemberAccount.Id=h.id 
insert into ListPvorSv
select ltrim(NEWID()),Mb.id,Mb.TimesMoney,0,'886FE96A-05F9-4F01-98B8-163FFD26B7BE',GETDATE(),'分红返利','SYS',1,'pv','',GETDATE(),'' from @member as Mb 

 

整理了一下放入存储过程，按时间进行执行

--创建存储过程
if (exists (select * from sys.objects where name = 'proc_memberRebate_streetok'))   
 drop proc proc_memberRebate_streetok
 go
 create proc proc_memberRebate_streetok
 as
   BEGIN
		declare @member table (
		id varchar(50),		   --用户ID
		TimesMoney decimal(18,2),--每日付款额度
		ReadyMoney decimal(18,2)--已返利
		)

		insert into @member 
		select M.Id ,U.BackBV , SUM(L.NomIn) as ReadyMoney from MemberAccount as M , UserStype as U ,ListPvorSv as L where M.DataState=1 and M.UserStype =U.Id and M.Id = L.MemberAccountId
		group by M.Id,U.BackBV

		--更新会员账户
		update MemberAccount set PV=PV+h.TimesMoney from @member as h where MemberAccount.Id=h.id 
		insert into ListPvorSv
		select ltrim(NEWID()),Mb.id,Mb.TimesMoney,0,'886FE96A-05F9-4F01-98B8-163FFD26B7BE',GETDATE(),'分红返利','SYS',1,'pv','',GETDATE(),'' from @member as Mb 
   end
   
 --执行存储过程
 exec proc_memberRebate_streetok  

 到此就已经完成每日会员表返积分，并更新向数据库插入记录

 

小计：

--取当前时间是星期几
select DATENAME(dw,GETDATE())

--个别处理
declare @dayName nvarchar(10)
set @dayName= DATENAME(dw,GETDATE())
if(@dayName!='星期五')
	begin
	  exec proc_memberRebate_streetok
	end

 

 

 

 

 

转载于:https://www.cnblogs.com/aaronguo/archive/2012/06/08/2542283.html