Date issues

Question

Post reply

Date issues

dfetrow

Valued Member

Points: 64
More actions
September 28, 2003 at 11:25 am

#106623

I am trying to make a stored procedure that will bring back the events that were schedulled on that day and 3 days after that day. I am running into questions how to handle reocurance. So lets say an event started today and the event will be there every first sunday of the month. And also another event shows up but reoccures every sunday untill a prescribed date. How do I build all those factors in one SP.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

Are you asking about jobs scheduled for the SQL Server Agent (i.e. msdb..sysjobschedules?

If not, I think we'd need more information about your schema...

--Jonathan

dfetrow Valued Member Points: 64 More actions · Answer 2

No. events. like every Tuesday We have a management meeting

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
No. events. like every Tuesday We have a management meeting

quote:
I think we'd need more information about your schema...

--Jonathan

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 4

Replace GetDate() with your date parameter your are interested in.



Set NoCount On

Create Table Test(ScheduledDate DateTime,SomeOtherCOl varchar(100))

Go

Insert Test

Select GetDate(),'Date Inside'

Insert Test

Select dateadd(day,3,GetDate()),'Date Inside'

Insert Test

Select dateadd(day,2,GetDate()),'Date Inside'

Insert Test

Select dateadd(day,4,GetDate()),'Date Outside'

GO

Select * 

    From Test

    Where ScheduledDate >= Cast(Convert(varchar(10),GetDate(),102) as DateTime) And

          ScheduledDate <  Cast(Convert(varchar(10),DateAdd(DAY,4,GetDate()),102) as DateTime)

    Order by ScheduledDate 

Go

Drop Table Test

Go

joelucas Ten Centuries Points: 1078 More actions · Answer 5

How do you represent th data in your database for events like "Management meeting every Monday"? Do you have a record for each occurrence or do you have a flag field that takes care of the repeated occurences?

Thanks

Lucas

dfetrow Valued Member Points: 64 More actions · Answer 6

Well I do not have the tables all built yet, but I was thinking of building a event table and then a recur table, because events can happen like every week on say tuesday and thursday. I need to have it work like Outlook

dfetrow Valued Member Points: 64 More actions · Answer 7

table1:

eventid

event

startDate

EndDate

recurFlag 'yes or no'

Table2

recurValID

recurVal

eventid

I need to do a select that will bring back 5 days of events. So getdate()+5....

then I need to join it with table 2 to see if the events are recurring.

for example: an event has a startDate of 09/22/03. That was last Monday. but it has a recur on every Monday and Wensday, with no EndDate. so it has 2 rows in table2 One row has a recurVal of 2 for Monday and a 4 for Wensday. but keep in mind. I have to bring 5 days back from today to view.