How do I do this??? Query/Design Question

[ShorePatrol]

I am creating a database that will keep track of scheduling....

So I need to store a START date/time stamp, and an END date/time stamp...

For example, if someone makes an appointment from 9 to 10 AM on 8/21/05, I'm assuming the database values should be:

SDATE = 8/21/05 9:00 AM
EDATE = 8/21/05 10:00 AM

Is this how I should do it? Or should I store the date & time seperately?

My big struggle here is how to query the information easily from the front end....

For example:
If I want to query all the events for 8/21/05, that would be easy......but if I want to display all the events on a web type calendar, I need to somehow figure out how to do this...

PYSDO CODE:

If there is an appointment that is at 9 AM, turn the table cell red
If there is an appointment that SPANS the 9am hour (like from 8-11), turn the table cell red

Any suggestions?

 

[DotNetGnat]

i would go with the date and time format that you have shown:

SDATE = 8/21/05 9:00 AM
EDATE = 8/21/05 10:00 AM

also can be easily dealt for web type calendar...

-DNG

 

[ShorePatrol]

Ok, but then how do I query it - if I have a table, and each row = a half hour increment, how do I turn the rows colors based on the events?

i.e.:
If there is an appointment that is at 9 AM, turn the table row red
If there is an appointment that SPANS the 9am hour (like from 8-11), turn the table row red

thanks

 

[Kendel]

When you display the Appts in TR, check if there exists an appt. Some thing like:

If Not IsNull(rs("appt")) or rs("appt")<>"" then
color = 'red'
end if

<tr bgcolor="<%=color%>"></tr>

 

[ShorePatrol]

You guys are missing my question....using your example:

If Not IsNull(rs("appt")) or rs("appt")<>"" then
color = 'red'
end if

I would have to do that for each time slot (ie. from 8 to 8:30, 8:30 to 9, etc) - which mean 48 queries in one page. I'm not sure that's the best way....perhaps 1 query with ALL the day's events, and then filter the query for each row.....?

But that's not really my question...

How do I detect "within" a time slot. For instance, if there is an event in the database like this:

EVENT_ID = 63
SDATE = 8/21/05 8:00 AM
EDATE = 8/21/05 1:00 PM

You can see this event goes from 8am to 1pm (5 hours)....now lets say I'm on the table row for 9:30 AM....this row should be turned to red....but how do I query it?

I need to somehow say:

Is there an event in the events table, for day 8/21/05, that SPANS the time of 9:30 AM....

How the heck do I do that?

 

[DotNetGnat]

we are not missing your question...we understood what you are trying to do...you need to write a code to check the starttime and endtime and loop through all the time intervals that you are showing on yuour calendar and then highlight all the intervals on the calendar...

-DNG

 

[ShorePatrol]

Ok -you understand it, but I need the code to do it,

The code is what my question is. No one seems to be able to give an example of how to detect within an event time, like my example above is asking....

Again...if there is an event in the database like this:

EVENT_ID = 63
SDATE = 8/21/05 8:00 AM
EDATE = 8/21/05 1:00 PM

You can see this event goes from 8am to 1pm (5 hours)....now lets say I'm on the table row for 9:30 AM....this row should be turned to red....but how do I query it?

I need to somehow say:

Is there an event in the events table, for day 8/21/05, that SPANS the time of 9:30 AM....

How the heck do I do that?

 

[DotNetGnat]

how is your web calendar structured...

do you show from 8:00 AM to 5:00 PM with 15 min intervals or how do you show??
depending on that we can write the code..

something like this to start...

dim starttime, endtime, hasevent

if hasevent<>"" then
'color the cells accordingly with the time intervals..

-DNG

 

[ShorePatrol]

It has 1/2 hour increments....

 

[ShorePatrol]

Let me give a better example:

Let's say I have data in an EVENTS table as follows:

EVENT_ID = 233
START_DATE = '8/21/05 8:00:00 AM'
END_DATE = '8/21/05 1:00:00 PM'
ROOM_ID = 5

How would I query to see if ROOM #5 was taken at 10 AM (Which in this case, it would be)....

 

[johnwm]

You could try a SQL query along the lines of:

Select Event_ID from tblEvents
Where Room_ID = 5
AND START_DATE <= querytime
AND END_DATE >= querytime

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[theniteowl]

ShorePatrol
You could do this all with SQL queries but are probably better off using one query to retrieve all records for the specified date then parsing the datetime field out so you can decide how each bit of data will display.

For your sql query you can use the Between statement to retrieve all records for a given date or date range.
Remember that "Between '12-23-05' AND '12-24-05' returns all records on the 24th AND the 25th.
Here is a good site about querying based on datetime fields.

http://www.databasejournal.com/features/mssql/article.php/2209321

If you have events that span multiple days remember that not only do you have to check FORWARD for the end date/time, you will have to make certain that you are checking backwards to the beginning of the event as well.
This is really only an issue if on your calendar display you are spanning visually across multiple days and have to know where on the screen you display the info so it does not overlap something else in the next day field. That really has a lot to do with how you are displaying your info though and may not apply.

Once you have the records you want you can look at the timedate field and consider only the time and determine how you want to display based on that.

What I would do is retrieve all of my records and store them in an array and close the recordset. Then you can at leisure loop through the array picking out the info you want and determining how to display it.
You can use a function like Split to separate the Date and Time portions at the space or you can use the Date functions to pick out the time for each one.

I am working on a web based calendar as well but have made it all the more difficult by emulating the Outlook style calendar where the monthly display shows and multi-day events graphically span across the calendar days.
Determining how they display means knowing if they are standard events, multi-day-all-day events or multi-day-part-day events and tracking what positions in each calendar day are open to display spanning events across so they do not overlap. Get's quite complicated but I will get there eventually.

 

[ShorePatrol]

There we go! Thanks!!!

 

[DotNetGnat]

more..

http://www.aspin.com/home/webapps/calendars

-DNG

 

[theniteowl]

ShorePatrol,
I assumed you would need to look up multiple days and multiple rooms. You might be better off grabbing all of your data in one query then parsing through it as you build each rooms listing or each days depending on how you display it. It would be a lot better than going back to the database over and over.

For instance, we have 40+ conference rooms. If we wanted to check every time frame for every conference room for every day of the week that would mean a whole LOT of individual queries to the database one after another.
Instead, I grab all event entries within a date range, store them in an array and then loop through that array pulling out info for each specific room and by date.

 

[ShorePatrol]

Yes, I will use an array....

I won't have any events that span across days, so that keep it a bit more simple.

Thanks for your suggestions! it should help a lot!

 

[theniteowl]

Your welcome.

johnwm's query works well to search by time but you can reverse that also so that you can search just by date.

WHERE START_DATE >= '2005-04-09'

This causes the datetime field to truncate to just the date.

so something like this will get all of your records for the specified date and order them by the date/time.

mydate = '2005-04-09'
mySQL = "SELECT * FROM tbl_events WHERE START_DATE >= '" & mydate & "' ORDERBY START_DATE"