Change status field value based on Date values

[vinamr]

Hi All,

I have a form that captures member registration details. In the admin section .. I would like to change the Status field of a record/s from NEW to PAYMENT DUE after 14 days from the data of submission. The status change should automatically trigger on the 15th day. So when the admin checks the list page he/she should be able to view the updated status field.

Any pointers how to accomplish this?

Thanks in advance.

Vinny

[Old Pedant]

What does your database look like?

What kind of database are you using?

Should be trivial if the DB is organized correctly.

[vinamr]

Hi OP,

I am using SQL Server 2005. The table has a DATE_REGISTERED field (mm/dd/yyyy). Also there is a STATUS field .. default value NEW.

So when a member registers today the status should change to PAYMENT DUE after 14 days. (provided the admin has received the payment and changed it to some other status).

Any pointers/code sample would be really aprpeciated. Thanks

Vinny

[Old Pedant]

Well, having it automatically change would mean you would need a scheduled task or job to update the DB at the appropriate time.

If you don't really care what the value in the DB is *UNTIL* you go look at it, then the obvious thing to do is perform the update each time you do look at that table.

It would probably be sufficient to perform the update each time you first hit whatever ASP page it is that shows you the status of various users.

So, *BEFORE* your code that does the SELECT to show user statuses, simply add in a call to this SQL:

UPDATE tablename
SET status = 'Payment Due'
WHERE status = 'New'
AND date_registered < DATEADD(d,-14,GETDATE());

That says "if the date_registered is prior to 14 days from right now *and* the existing status is still 'New' *then* change the status."

[vinamr]

Thanks OP .. I would like to automate this process thru some kind of a script/function. I don't want the status to remain NEW until and unless someone clicks on a list page.

Do you think this can be accomplished thru a DB trigger / Procedure??

THanks
Vinny

[Old Pedant]

A trigger is only triggered when a *DATABASE* event occurs.

If nobody is using the DB, then no database events are occurring.

Yes, you could and would want to use a stored procedure (containing the SQL code I showed you), but if you want to have it happen on some kind of schedule, you will have to learn how to schedule SQL Server jobs. Or use the task scheduler.

But that, too, if fraught with peril. Let's suppose that you run a scheduled task once per hour that goes and finds all the records that need to be updated.

And now let's suppose that the task ran at 1:00 PM. And you have somebody who is due to turn from NEW to DUE at 1:15PM. And then you login at 1:30PM.

Guess what! That person will still show as NEW and not as DUE, because he/she won't get updated until the next run of the scheduled task at 2PM.

Truly and honestly, you will be BETTER OFF--and the code and task will be easier--if you simply do it each time you are about to look at the table. It's really a very low overhead UPDATE, especially if you index the STATUS field. Because then only records that still show NEW will even be looked at.

[vinamr]

Hi OP .. I scheduled a simple job on the DB to run everyday. I think thats going to work fine considering the limited number of applications we receive online.

Thanks for your help - Vinny