Questions:

Is '" &this_claday_starttime& "' the start time of the class in question or the start time of the day for all classes? Same question for '" &this_claday_endtime& "'

What does " &this_claday_datenumber& " represent?

What variable amount of time is used for '" &this_claday_starttime_plus_sec& "' and '" &this_claday_endtime_minus_sec& "'?

If you'll notice in the reformatted code below, I ask if certain conditions in the where clause are repeats. Can you confirm that they're just cut/paste errors and I didn't foul up the parenthetical removals?

'" &this_time_offset_start& "' is just '" &this_claday_endtime& "' + 40 minutes and '" &this_time_offset_end& "' is just '" &this_claday_starttime& "' - 40 minutes, right?

Code reformatted for anyone else who's going through this, I've already started parameter swapping and cleaned out a bunch of extraneous parantheses in the where clauses to help with readibility. My guess is this is going to be a two pass query because the information described is used differently. One confirms availability, another produces warnings for another screen, if I understood Bullo right.

DECLARE @Today DATETIME, -- Replacing '" &this_claday_date& "'

@ClassStarttimeDATETIME -- Replacing '" &this_claday_starttime& "'

-- this simply strips the time off the datetime stamp

-- Replace GetDate() with your queried date from the parameter call, making this simple for now.

SET @Today = DATEADD( dd, DATEDIFF( dd, 0, getdate()), 0)

SELECT CoachID FROM coaches WHERE active=1

-- Availability Check

SELECT DISTINCT

tblCoachAvail.*

FROM

tblCoachAvail

WHERE

( tblCoachAvail.av_day = 0

OR tblCoachAvail.av_day = "&this_claday_datenumber&"

AND tblCoachAvail.av_datefrom <= @Today

AND tblCoachAvail.av_dateto >= @Today

AND tblCoachAvail.av_timefrom <= '" &this_claday_starttime& "'

AND tblCoachAvail.av_timeto >= '" &this_claday_endtime& "'

AND tblCoachAvail.av_not = 0

AND tblCoachAvail.av_coach = " &rsdetails("coachid")& "

-- Vacation Check

SELECT

tblCoachAvail.*

FROM

tblCoachAvail

WHERE

tblCoachAvail.av_not = 1

AND (tblCoachAvail.av_day = 0

OR tblCoachAvail.av_day = " &this_claday_datenumber& ")

AND tblCoachAvail.av_datefrom <= @Today

AND tblCoachAvail.av_dateto >= @Today

AND ( (av_timefrom >= '" &this_claday_starttime& "'

AND av_timeto <= '" &this_claday_endtime& "')

-- Think this is a repeat of the above one

OR (av_timefrom <= '" &this_claday_starttime& "'

AND av_timeto >= '" &this_claday_endtime& "')

OR (av_timefrom <= '" &this_claday_starttime& "'

AND av_timetoBETWEEN '" &this_claday_starttime_plus_sec& "'

AND '" &this_claday_endtime& "')

OR (av_timeto>= '" &this_claday_endtime& "'

AND av_timefrom BETWEEN '" &this_claday_starttime& "'

AND '" &this_claday_endtime_minus_sec& "') )

AND av_coach = " &rsDetails.Fields("coachid")"

-- Period checks/conflicts

SELECT

tblClassDays.classday_kids,

tblClassDays.classday_starttime,

tblClassDays.classday_endtime,

tblClasses.cla_Location

FROM

tblCoachClassDays

LEFT JOIN

tblClassDays

ONtblCoachClassDays.coachclass_cladayID = tblClassDays.classday_id

LEFT JOIN

tblClasses

ONtblClassDays.classday_classID = tblClasses.cla_ID

WHERE

tblClassDays.classday_noclass = 0

AND tblClassDays.classday_day = @Today

AND ( (classday_starttime >= '" &this_claday_starttime& "'

AND classday_endtime<='" &this_claday_endtime& "')

-- Repeat again?

OR (classday_starttime<='" &this_claday_starttime& "'

AND classday_endtime>='" &this_claday_endtime& "')

OR (classday_starttime<='" &this_claday_starttime& "'

AND classday_endtimeBETWEEN '" &this_claday_starttime_plus_sec& "'

AND '" &this_claday_endtime& "')

OR (classday_endtime>='" &this_claday_endtime& "'

AND classday_starttimeBETWEEN '" &this_claday_starttime& "'

AND '" &this_claday_endtime_minus_sec& "') )

AND coachclass_coachid = &rsDetails.Fields("coachid")

-- Time deviation warnings

SELECT

cla_Location,

classday_starttime,

classday_endtime

FROM

tblCoachClassDays

LEFT JOIN

tblClassDays

ONtblCoachClassDays.coachclass_cladayID = tblClassDays.classday_id

LEFT JOIN

tblClasses

ONtblClassDays.classday_classID = tblClasses.cla_ID

WHERE

tblClassDays.classday_noclass = 0

AND tblClassDays.classday_day = @Today

AND (tblClassDays.classday_starttimeBETWEEN '" &this_claday_endtime& "'

AND '" &this_time_offset_start& "'

OR tblClassDays.classday_endtimeBETWEEN '" &this_time_offset_end& "'

AND '" &this_claday_starttime& "')

AND coachclass_coachid = &rsDetails.Fields("coachid")