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, ...

DateTime

Hello,

Please can you point me in the right direction.

I have a table (JobMain) that holds a list of all jobs that out
engineers need to work on or have worked on.

I would like to select jobs that fall between Monday - Friday 8am to
7pm.

The tables holds a load of colums ... the two that are of interest to
me are

JobId (Primary Key) - Job Number
StartDate (Datetime datatype) - Job start date and time

regards

RobOn Jun 19, 4:21 pm, roblowein <rob.low...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
Please can you point me in the right direction.
>
I have a table (JobMain) that holds a list of all jobs that out
engineers need to work on or have worked on.
>
I would like to select jobs that fall between Monday - Friday 8am to
7pm.
>
The tables holds a load of colums ... the two that are of interest to
me are
>
JobId (Primary Key) - Job Number
StartDate (Datetime datatype) - Job start date and time
>
regards
>
Rob

DECLARE @.startdate datetime ,@.enddate datetime

SET @.startdate = '2007-06-18 08:00:00' -- Monday 8 AM
SET @.enddate = DATEADD(DD,4 ,DATEADD(HOUR,11,@.startdate)) --

SELECT @.startdate,@.enddate

SELECT * FROM yourtable
WHERE StartDate BETWEEN @.startdate AND enddate|||Hello, Rob

Try something like this:

SELECT * FROM YourTable
WHERE DATEPART(hh,StartDate) BETWEEN 8 AND 18
AND (DATEPART(w,StartDate)+@.@.DATEFIRST)%7 BETWEEN 2 AND 6

Razvan|||On 19 Jun, 12:56, Razvan Socol <rso...@.gmail.comwrote:

Quote:

Originally Posted by

Hello, Rob
>
Try something like this:
>
SELECT * FROM YourTable
WHERE DATEPART(hh,StartDate) BETWEEN 8 AND 18
AND (DATEPART(w,StartDate)+@.@.DATEFIRST)%7 BETWEEN 2 AND 6
>
Razvan

Thanks to you both...

A lot of help..

Regards

Rob