time sheduling - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

time sheduling

Author

Topic

safderalimd
Starting Member

32 Posts

Posted - 2009-09-23 : 09:14:27

Is it possible to write T-SQL to generate time scheduling records?

For example if we want to schedule an event starting from '2009-09-23 10:00am' to '2009-09-24 9:00am' with interval of every 60 minutes, is it possbile to generate schedule details record of this event in single T-SQL without using cursor/loop?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-23 : 10:18:13

Do you mean something like this? Or are you trying to schedule a job that fires off a sproc every hour?
DECLARE @Start datetime,@stop datetime
set @start = '2009-09-23 10:00am'
set @stop = '2009-09-24 9:00am'

SELECT
DATEADD(hour,spt.number,@Start)
FROM
master..spt_values spt
WHERE
[type] = 'P'
and spt.number <= DATEDIFF(Hour,@start,@stop)

Jim

Everyday I learn something that somebody else already knew

safderalimd
Starting Member

32 Posts

Posted - 2009-09-23 : 10:48:33

My issue has nothing to do with SQL server scheduling. Its application sheduling.

Here is example of what I am looking for.

Question) Generate schedule details for event_id = 1 starting from '2009-09-23 10:00am' to '2009-09-23 1:00pm' at interval of 30minutes.

I need single T_SQL to analyze above query and generate following records

(event_id) (schedule_time)
---------------------------
(1) ('2009-09-23 10:00am')
(1) ('2009-09-23 10:30am')
(1) ('2009-09-23 11:00am')
(1) ('2009-09-23 11:30am')
(1) ('2009-09-23 12:00pm')
(1) ('2009-09-23 12:30pm')
(1) ('2009-09-23 1:00pm')

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-23 : 11:57:51

Sorry, I don't understand what you're looking for. What is wrong with result set that my query produces?

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-24 : 02:48:06

quote:
Originally posted by safderalimd

My issue has nothing to do with SQL server scheduling. Its application sheduling.

Here is example of what I am looking for.

Question) Generate schedule details for event_id = 1 starting from '2009-09-23 10:00am' to '2009-09-23 1:00pm' at interval of 30minutes.

I need single T_SQL to analyze above query and generate following records

(event_id) (schedule_time)
---------------------------
(1) ('2009-09-23 10:00am')
(1) ('2009-09-23 10:30am')
(1) ('2009-09-23 11:00am')
(1) ('2009-09-23 11:30am')
(1) ('2009-09-23 12:00pm')
(1) ('2009-09-23 12:30pm')
(1) ('2009-09-23 1:00pm')

DECLARE @Start datetime,@stop datetime
set @start = '2009-09-23 10:00am'
set @stop = '2009-09-23 1:00pm'

SELECT
DATEADD(minute,spt.number/2.0*60,@Start)
FROM
master..spt_values spt
WHERE
[type] = 'P'
and DATEADD(minute,spt.number/2.0*60,@Start) <= @stop

Madhivanan

Failing to plan is Planning to fail

Kabila
Starting Member

33 Posts

Posted - 2009-09-24 : 03:20:11

Do you mean like this?

Declare @start datetime,@stop datetime,@Interval datetime
set @start = '2009-09-23 10:00am'
set @stop = '2009-09-24 9:00am'
set @Interval = '1900-01-01 0:30am' ;

with CTE as
(select @Start as Starttime
union all
select Starttime+@Interval as Starttime from CTE where Starttime+@Interval<=@stop
) select * from CTE
;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources