sql server 2008 - manipulating dates - week window.

Question

If I want to make sure an operation can only be done once a week by a user, what is an efficient way to do it? If a user has carried out that operation on Friday, then he can still do it on Tuesday, because it's "next" week.

Answer 1

In a real general sense you can do this. It assumes a History Table with at least one column [Last_Date_Ran]

1. Begin process
2. check the history table for a date within current Sun-Sat
3. If a record extists in step 2 exit else perform process.

Answer 2

Your users do not have direct access to your database server, there is a UI through which this operation is performed... correct?

Therefore, the efficient way is to have an "OperationLastPerformed" (of type `datetime) column in your table and to populate that field when the operation is performed.

At that point, which ever programming language is used for your UI, it will be easy (and proper) to enforce that piece of business logic from your code...

If that is not acceptable and this must be done from the backend you could create a trigger that would check the "OperationLastPerformed" field before commiting the record and if the datetime is within current week rollback the commit...

Answer 3

Create a table that stores a history of user actions:

CREATE TABLE UserActions (
   userId int,
   weekOfYear int,
   year int
)

To check if the user has performed an action during the same week, you can use the DATEPART function to determine the week:

IF EXISTS (SELECT * FROM UserActions WHERE userId = @userID and weekOfYear = DATEPART(isowk, GetDate() AND year = Year(GetDate())

If that returns NULL, the user hasn't performed any actions in the current week. Then, you could insert a row after the action is performed.

INSERT INTO UserActions (userId, weekOfYear int, year) 
VALUES (@userId, DATEPART(isowk, GetDate(), Year(GetDate())