Removing Data From Tables Automatically 1

[aislancezar]

Guys,
Good morning everyone!

Good staff, to needing a little help from you.

I have two tables I need to remove data automatically.
To add data, even I would add, just need something that removes the data so that win 30 days.

example:
'm Doing at the moment as follows.
A user makes a donation of a certain sum of money and get a VIP account on my site and will have certain rights for 30 days.
Alas, I note in a notebook the day he will win the VIP user, I manually in sql server 2008 r2 standard and add the data manually and when they complete these 30 days, I have to go back and delete data .


Example:

Name of the 1st table: user_dir
Columns: user_id, nome_user, nick_user, level;
Actions: suppose there is already a registered user on my site: user_id = '1', name_user =' Charles ', nick_user =' Carlito ', level = '1';
Then I add the prefix [VIP] nick_user the number 30 and the level of the user eg nick_user = '[VIP]Carlito', level = '30 ';

Name of 2nd table: user_direitos
Columns: user_id, direito_id, direito_nivel, date;
Action: Add the following data tables: user_id = '1 ', direito_id = '8', direito_nivel = '30 ', date = '22/11/2010 12:06:00' (date of registration as vip);

Description of table columns: user_dir
user_id: ID number of the registered user.
nome_user: user login name.
nick_user: Display name of the user on the site.
Level: Level of knowledge.

Description of table columns: user_direitos
user_id: ID number of the registered user.
direito_id: Identification of privilege.
Example: direito_id = '1 '-> privilege of access to restricted areas of reading
Example2: direito_id = '2 '-> access privileges to read and post access to restricted areas
direito_nivel: privilege level -> Custom level of privilege.
Date: The date that the privilege was added to the VIP user.

Then, after having passed the 30 days from the date that I signed up as a VIP user, something that would automatically remove these data from two tables mentioned above, and the user back to being a normal user.

Here is an example of what I would do the job:
Run every day at 23:59:00
Check the column in the table user_direitos date
If the date on which the user was added as vip reached 30 days, then remove the following data in columns and tables below:

Remove prefix [VIP] column of the table nick_user user_dir.
Remove value 30 column in the table level and add value user_dir 1.
Delete a value of user_id column in the table user_direitos.
Delete the value 8 in table column direito_id user_direitos.
Delete the value 30 in table column direito_nivel user_direitos.
Delete date 10/22/2010 22:00:00 column in the table user_direitos date.

Okay, now the user returns to a normal user.


Is it possible? Is there a way, or something that removes the data automatically once the 30 days being completed?

Can someone show me how to do this?

Now, thank you everyone's attention.

 

[ousoonerjoe]

Look at the DATEDIFF() function. It will tell you when the start date versus today's date is 30 or more.

Might I also suggest (space provided) that you do not do physical deletes for say 90 days (if ever) in the event you need to do corrections on an account that was "misflagged" for delete. It will also allow you to perform history reports against users, VIP's etc that can help you identify trends and possibly areas of interests that could be improved.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[aislancezar]

Hello friend, first of all, thanks for the help.
Another thing, I do not plan on sql in this case, you could set an example of a job or show me the step by step how to do this?

But I want to delete the same data.


Thanks.

 

[ousoonerjoe]

If you are trying to set this up as an automated process in SQL 2008 r2, then you need to look at the SSIS processing. The forum is forum1555.

As for the process of identifying the records to delete/mark, I would suggest using a stored procedure to manage the processing and call it from your job in SSIS.

Example of usage for DATEDIFF() is as follows:

UPDATE Table1
SET DeleteFlag = 1
WHERE [COLOR=pink]DATEDIFF[/color](DAY, StartDate, EndDate) <= 30;

Are the 2 tables (user_dir, user_direitos) a One-to-One relationship or a Many-to-One? The following is an example to update the user_dir table and should point you in the right direction for the rest (code is NOT tested and may need to be tweeked slightly for your needs):

UPDATE user_dir
SET nick_user = REPLACE(nick_user, '[VIP]', ''),
	level = ''
WHERE user_id IN (SELECT u.user_id
		FROM user_dir u
			INNER JOIN user_direitos d ON u.user_id = d.user_id
		WHERE DATEDIFF(DAY, d.date, GETDATE()) >= 30);

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[aislancezar]

Yay, I test here and I'll soon be posting the result.

Already, thanks ousoonerjoe.