Sorry about posting this to the wrong list, but I'm having trouble getting
mail through to the SQL list.
I am running a query to see if there is an entry in the database for an
event that matches certain date criteria. One of the criteria is that one
of the dates that the event is scheduled on does not fall within a certain
list of dates that are stored in the "exception_dates" table.
All of the query works except for: AND NOT EXISTS (SELECT 1
FROM
exception_dates ed
WHERE
ed.exception_dates = '#year#-#month#-#week_day# 00:00:00'))
The way it should work is only return a record if there is no instance of
the current year-month-day matching anything in the exception_dates table.
Here is the whole query:
SELECT 1 FROM dummy WHERE EXISTS
(SELECT 1 FROM teacher_schedule ts
WHERE ts.contact_id = #Request.TempContactID#
AND '#year#-#month#-#week_day#' BETWEEN ts.start_date AND
ts.end_date
AND (ts.start_time_1 <> ts.end_time_1 OR ts.start_time_2 <>
ts.end_time_2)
AND (SELECT days
FROM days_times dt
WHERE dt.days_times_id = ts.start_time_1) =
'#DayOfWeekAsString(week_day)#'
AND NOT EXISTS (SELECT 1
FROM
exception_dates ed
WHERE
ed.exception_dates = '#year#-#month#-#week_day# 00:00:00'))
What am I missing?
Todd Ashworth
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
