如何每个新月自动更新一个表？ [英] How to auto Update a table every new month?

问题描述

我有促销大师表，其中包括会员，银牌，金牌，绿宝石，钻石和皇冠等促销，

每个用户在注册时都被视为会员。

我必须每月在注册表中推广他们的促销活动





我有一张桌子，如图所示，



促销主表：

I have a Promotion Master table with promotion like Member,Silver,Gold,Emerald,Diamond and Crown etc.,
Each users consider as a "Member" at their registration.
I have to promote their promotion every month in Registration Table


I Have a tables as shown,

Promotion Master Table:

Promotion_Id         Promotion_Name        

1                     Member
2                     Silver
3                     Gold
4                     Emerald
5                     Diamond
6                     Crown

用户注册表：

User Registration Table:

UserId                  Name                Promotion_ID

1                     Rajesh                Member
2                     Nelson                Silver

促销ID应每月自动更新到下一级促销。



Ex：



如果客户在2014年8月31日注册成为会员，则其促销活动应于2014年9月1日从会员更改为银牌



如果它是白银它应该改为黄金



我正在使用Sql server 2008R2



在此先感谢，

Rajesh

Promotion Id should auto update every month to next level of promotion.

For Ex:

If a customer register as a Member in 31 st August 2014 , his promotion should change from "Member" to "Silver" on September 01 2014

If it is "silver" it should change to "Gold"

I'm using Sql server 2008R2

Thanks in Advance,
Rajesh

推荐答案

编写一个存储过程，执行所需的任务，然后将该过程设置为通过计划执行。



您可以通过SQL Enterprise Manager通过选择相关服务器来设置计划，进入管理SQL Server代理作业。在这里，您可以创建作业以在特定时间表运行存储过程。



这是您的程序的一部分，您可以使用。

write a stored procedure that will perform your required tasks and then set that procedure to be executed via a schedule.

You can set schedules via SQL Enterprise Manager by selecting the relevant server, go into Management SQL Server Agent Jobs. In here you can create the jobs to run your stored procedure at a specific time schedules.

Here is part for your procedure, you can use.

create procedure dbo.update_table
as 
begin 
update PromotionMasterTable
set Promotion_Name ='Silver'
where Promotion_Name ='Member'

运行工作 [ ^ ]在每个月的月初为你完成任务。

Run a job[^] that does the task for you at the beginning of every month.

我创建了一个新表进行记录，这里我更新了两个上个月推广和新更新的每个用户的促销活动更新日期。



i提供我的更新表，



U_Id user_id from_promotion to_promotion Updated_date







i检查更新表是否有针对当前月份和年份更新的所有用户的任何行。



如果没有更新，



我只需从数据集中的用户注册表中获取所有的promotion_id和user_id，并更新全部对于用户注册表中的每个用户，promotion_id为（promotion_id = promotion_id + 1）。



最后我更新了来自id和更新表中的id，如



U_Id user_id from_promotion to_promotion更新日期



1 1 1 2 2014-09-08

2 2 2 3 2014-09-08





谢谢，



Rajesh

I created one new table for record,here i update both last month promotion and newly updated promotion for each users with updated date.

i provide my Update table,

U_Id user_id from_promotion to_promotion Updated_date



i check the update table whether there is any row for all users updated for current month and year.

If not updated,

I just take all promotion_id and user_id from the user registration table in dataset,and update all promotion_id as(promotion_id=promotion_id+1) for each users in user registration table.

finally i update the from id and to id in update table like,

U_Id user_id from_promotion to_promotion Updated_date

1 1 1 2 2014-09-08
2 2 2 3 2014-09-08


Thanks,

Rajesh

这篇关于如何每个新月自动更新一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！