Check available Time procedure

Hey Guys,
I hope someone can help on here with this. I have this Database where techs are scheduled and dispatched to perform tasks based on skus. What I am trying to achieve is finding the first available Tech based on their schedule and the appointments table.
Example User enters today's date and 5:30 AM and the search for all available techs to perform that task

the tables ddl is
USE [Schedule]
GO
/****** Object: Table [dbo].[AllDays] Script Date: 05/31/2006 01:13:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AllDays](
[ID] [int] NOT NULL,
[DayString] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_WorkingDays] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
USE [Schedule]
GO
/***Appointments Table where trouble is *****
/****** Object: Table [dbo].[Appointments] Script Date: 05/31/2006 01:17:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Appointments](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Customer_ID] [int] NOT NULL,
[Tech_ID] [int] NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL,
[App_Date] [datetime] NOT NULL,
[Created_By] [int] NOT NULL,
[Date_Created] [datetime] NOT NULL,
[Sku_ID] [int] NOT NULL,
[Comment_ID] [int] NOT NULL,
CONSTRAINT [PK_TechsShifts] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [Schedule]
GO
ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Comments] FOREIGN KEY([Comment_ID])
REFERENCES [dbo].[Comments] ([ID])
GO
ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Techs] FOREIGN KEY([Tech_ID])
REFERENCES [dbo].[Techs] ([ID])

USE [Schedule]
GO
/****** Object: Table [dbo].[Schedule] Script Date: 05/31/2006 01:19:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Schedule](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Tech_ID] [int] NOT NULL,
[AllDayID] [int] NOT NULL,
[ShiftStartTime] [datetime] NOT NULL,
[ShiftEndTime] [datetime] NOT NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [Schedule]
GO
ALTER TABLE [dbo].[Schedule] WITH CHECK ADD CONSTRAINT [FK_Schedule_AllDay] FOREIGN KEY([AllDayID])
REFERENCES [dbo].[AllDays] ([ID])

Plus the Techs Table which holds their ID's names etc
I have a snapshot (http://webdivisions.net/images/relation.gif) of the relationship posted here if that can help

Thanks for any inputDefine "first available tech".
Does an appointment have to fall completely within a single tech's shift?
Can an appointment span a day?
And why are you using varchar to store date values?

Look, a basic query would look like this:

select top 1 Schedule.Tech_ID
from Schedule
inner join Appointments
on Schedule.ShiftStartTime <= Appointments.StartTime
and Schedule.ShiftEndTime <= Appointments.EndTime
where not exists
(select *
from Appointments Committed
where Commited.TechID = Schedule.Tech_ID
and ((Committed.ShiftStartTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Committed.ShiftEndTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Appointments.StartTime BETWEEN Committed.ShiftStartTime and Committed.ShiftEndTime)))|||Thanks for the reply blindman,
What I meant by first tech can be best explained with this scenario.
End user selects date as 06/02/2006 at 10:00 AM then search. the query should return all available techs for that date and period of time and the closest time available if 10:00 AM is not available on that day.
Example*******************
Tech available from date available
Tech1 10:00 AM 06/02/2006
Tech2 11:00 AM 06/02/2006
tech2 9:00 AM 06/02/2006
************************
now as for your question
>Does an appointment have to fall completely within a single tech's shift?
Yes, all techs will be required to reschedule their appointment if they cant finish them on day assigned.
Can an appointment span a day?
No they shouldnt, its just not within the nature of the business to do this.
And why are you using varchar to store date values?
I cant see where you seen that. I know varchar for dates will complicate things for me so I stayed away from doing that.

I hope that answers your questions.

Thanks for your time|||...
And why are you using varchar to store date values?
I cant see where you seen that. I know varchar for dates will complicate things for me so I stayed away from doing that.

"[DayString] [varchar](20) "??

Will the logic I gave you in my sample code work for your situation?|||"[DayString] [varchar](20) "??
Oh this table hold an integer representing the day and DayString is the day itself.
**************************
ID DayString
1 Sunday
2 Monday
**************************
I tried to run the SQL the following

select top 1 Schedule.Tech_ID
from Schedule
inner join Appointments
on Schedule.ShiftStartTime <= Appointments.StartTime
and Schedule.ShiftEndTime <= Appointments.EndTime
where not exists
(select *
from Appointments Committed
where Committed.Tech_ID = Schedule.Tech_ID
and ((Committed.StartTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Committed.EndTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Appointments.StartTime BETWEEN Committed.StartTime and Committed.EndTime)))

the result was no records. Am I missing something here?
Thanks for your help|||Logic error. Should have been:
select top 1 Schedule.Tech_ID
from Schedule
inner join Appointments
on Schedule.ShiftStartTime <= Appointments.StartTime
and Schedule.ShiftEndTime >= Appointments.EndTime
.........to find shifts that bracket the appointments.|||Thanks Blindman :-)
After the I changed the script to what you recommended, The statements returned one row.
Now the stupid question, Is it possible to return the top 1 then the closest available techs as well?
IE, If user enters 12/02/2007 10:30 AM and we have tech1 who starts at 9:30 AM on that day and have no appointment but tech2 starts 12:00 PM. and have an appointment starting 12/02/2007 1:30 PM and ending 12/02/2007 4:00 PM. is it possible to list tech2 with his/her availablity. this way the user doesnt have to go back and query the database again by changing date and time.

second stupid question and that is related to my lack of experience in SQL is how can send this SP the date and time to look for.

Thank you very much for your help.|||In answer to your first question, just about anything can be done in SQL. But you are getting into some fairly complex business logic that is particular to your application. It will require a substantial amount of effort in requirements gathering and programming to get this right. You either need to get educated on SQL fast, or hire a contractor to do the work for you.

To answer your second question, the datetime value can be sent to the stored procedure as a parameter. Please read about stored procedures in Books Online.|||Thank you for all your help blindman
You have been great

I'll be hitting the book store today!|||Hit Books Online first. It's free.