DateTime Query

Hi, am trying to build a scheduling system within my SQL Server application. Can someone point me in a good direction please?

OK, A user can select that they want something to happen Weekly, and on each Tuesday of every week. They of course can select any day from Monday through to Sunday. I would like to know how to take this data, and through a stored procedure update a table to set the "next execution date".

I have sorted the Daily timetable for each time, and the Monthly on a certain date seems easy enough, but I cant get the Weekly on a certain Day sorted. Any advice would be great!

Maybe you could post some code of your table and query...?

Though I'm not sure why you have multiple tables; monthly, weekly, daily.

You should just need one

NextExecutionMgr( DueDate datetime, FreqIntvl varchar(2), FreqAmt int, RecordKey varchar(200) )

index on DueDate, most likely a second index on RecordKey

The first item on your DueDate index is the next one to be processed.

When its time comes and once it is processesed you just adjust the date:

Code Snippet

case FreqIntvl when 'dy' then DueDate = DateAdd(dy, FreqAmt, DueDate)

when 'wk' then DueDate = DateAdd(wk, FreqAmt, DueDate)

etc.

end

(doesn't it suck that dateadd doesn't accept a variable for parameter one?)

|||

Why re-invent the wheel?

I would recommend exploring the SQL Agent Service, since it has full features calendaring and scheduling already built-in.

And if you are using SQL 2005 Express, which doens't include SQL Agent, you could explore a combination of using the Windows Scheduler service and SQLCmd.exe.

|||

Arnie, quite true.

I guess it just depends on what it is he's trying to schedule.

Agent is perfect for scheduled system level events and tasks.

But if he's trying to kick off application events with 1,000's of users, that a different thing.

Lotsa cats...

|||

And the skin just regrows...

I suspect that the solution will evolve into a combination of efforts -your outline about how to manage a 'queue' table, and some form of a scheduled process to 'POP' the queue.

There just isn't enough information to point the OP in the 'best' direction. SQL Agent, Notification Service, Service Broker Queues, some 'homegrown' hybrid, ...