How to auto Update a table every new month?

Question

5.00/5 (2 votes)

See more:

SQL-Server-2008R2

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:

SQL

Promotion_Id         Promotion_Name        

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

User Registration Table:

SQL

UserId                  Name                Promotion_ID

1                     Rajesh                Member
2                     Nelson                Silver

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

Posted 26-Aug-14 23:20pm

Rajesh waran

Updated 27-Aug-14 0:22am

v4

Add a Solution

3 solutions

Solution 1

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.

SQL

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

Posted 26-Aug-14 23:31pm

Gihan Liyanage

Updated 26-Aug-14 23:34pm

v3

Comments

Magic Wonder 27-Aug-14 5:40am

@...R@JES, you need to take care while building your logic. If you start updating Members to Silver First and in same sequence then this will leave you all members promoted to CROWN in one Go.

Rajesh waran 27-Aug-14 6:33am

I'm using Sql-server 2008R2,I couldn't find sql agent in my sql server. how to install ?

Gihan Liyanage 27-Aug-14 6:41am

Express Edition ???

Gihan Liyanage 27-Aug-14 6:41am

see this Link

http://sqlblog.com/blogs/tibor_karaszi/archive/2011/02/10/what-does-this-express-edition-look-like-anyhow.aspx

Rajesh waran 27-Aug-14 6:42am

I'm using Sql-server 2008R2,I couldn't find sql agent in my sql server. how to install ?

Rajesh waran 27-Aug-14 6:43am

Yes i'm using express edition

Gihan Liyanage 27-Aug-14 6:47am

Express Edition is not having SQL Agent , :-O

Rajesh waran 27-Aug-14 6:49am

How to solve this problem?

Gihan Liyanage 27-Aug-14 6:53am

Please read this article. This includes how to run SQL Jobs, in SQL Express,

http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express

Gihan Liyanage 27-Aug-14 7:01am

How is the progress ? , This is part 2.
http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express-2

Rajesh waran 8-Sep-14 5:29am

Thanks,it was very useful to me.but i made my way simple.I provide the solution below

Solution 2

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

Posted 27-Aug-14 0:24am

Abhinav S

Comments

Rajesh waran 27-Aug-14 6:42am

I'm using Sql-server 2008R2,I couldn't find sql agent in my sql server. how to install ?

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rajesh waran · Accepted Answer · 2014-09-07T23:50:00

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

How to auto Update a table every new month?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

How to auto Update a table every new month?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us