Employee schedule

[TheNewGuy2]

Hey Guys,
I wonder if anyone have had any experience with this situation. its more of an SQL question but I can not find any answer to it anywhere and hoping someone here can help.
I need to create an employee shedule for dispaching techs to do jobs in a 30 minutes intervals.
this is what I have so far
employee table
ID
fName
lName
weekdays table
ID
Monday "Start time example 12:00 PM"
tuesday "start time 9:00 AM"

Service calls table
ID
TechID
CustomerID
Start Time
End Time
Date
--
My problem here is I want to be able to see the time the tech available to perform service calls, for example
if Tech1 has a service call on 04/22/06 starting 9:00 AM and ending 10:30 AM I want to be able to book him/her for the times they are available and not override the calls they already booked for.
any clues or any ideas will be much appreciated
Thanks

[kabbi_thkek]

Hello,

May I ask you a question about your database table structure and query's you used? I'm also trying to make a employee schedule and would like to show this schedule on the webpage in a table. The problem is that I would like to show a week schedule where the columns are the days of the week. I can't get it to work. The problem is t do a right query.

I use the following query:
SELECT c.*, u.*, l.* FROM cal AS c
LEFT JOIN usrs AS u ON c.u_id = u.u_id
LEFT JOIN lo AS l ON l.l_id = u.lo WHERE WEEKOFYEAR('') = $weeknumber

Can you maybe give me any advise?

Thank you in advance.

Regards,

Kabbi

[Newsgroup_User]

Let us see your table structures

[kabbi_thkek]

Hi,

Thank you very much. Until now I use the following tables:

table calendar:
ag_id int
event
u_id
event_type
received [Date]
received2 [Time IN]
event_finish2 [Time OUT]
group

table location
lok_id
lok_building
lok_room
Loc_descr

table users:
u_id
u_location
u_room
u_department
u_type
u_min [min hours per week]
u_max [max hours per week]
u_hours [total hours worked this week]
u_notes
u_function
u_telephone
u_email

Then I use the following query:

SELECT c.*, u.*, l.* FROM calendar AS c
LEFT JOIN users AS u ON c.u_id = u.u_id
LEFT JOIN location AS l ON l.l_id = u.lo WHERE WEEKOFYEAR('') = $weeknumber

Probably this is not right if I want to show when the employees work in the given week for a specific department.
I would like to show a week table on the screen with the columns as the days of the week. For each day [column] I would like to show the user that worked and the time in and out. I also want to calculate the total working time for that employee per day and per week, but that is a different matter...

I hope you understand what I mean and could help me in the right direction. Is this even be possible?

Thank you in advance.

Regards,

Kabbi