?
Solved

Help with QofQ to check schedule for conflicting appointments - maybe use loop?

Posted on 2006-04-27
4
Medium Priority
?
202 Views
Last Modified: 2013-12-24
I am building a scheduling application for test equipment, using ColdFusion MX and MS Access. I've already built the templates for adding and viewing appointments.  What I'm working on now is the code to check for conflicting appointments. When a user wants to schedule time with test equipment (the appointment) they select a tester, a date, a start time and end time. I want CF to check and see if there are any conflicting appointments already scheduled.

My first query checks to see if there are any appointments on the selected date with selected equipment:

<CFQUERY NAME="qGetAppts" DATASOURCE="#variables.EVT#">
SELECT schedID, testEquip, evtDate, startTime, endTime
FROM tblSchedule
WHERE evtDate = #createODBCDate(CLIENT.apptDate)#
AND testEquip = #form.tester#;
</CFQUERY>

If no records are returned, then I don't need to check for conflicts, and I can continue with adding the new appointment to the database.  This part I know how to do.

If there are appointments on said date/equipment, then I would need to check for conflicting appointments.

Basically, I'm thinking the logic should be:
- start time cannot begin within an already scheduled appointment
- end time cannot end within an already scheduled appointment
- the new appointment cannot overlap an already scheduled appointment (meaning if an appointment is already scheduled from 1pm to 3pm with Tester A, a new appointment cannot be scheduled for Tester A from 10am to 5pm)

Here's the QofQ that I started.  Should I build on this or scrap it and go with a cfloop?  Either way I need help, please :) I don't know much about loops.

<cfif qGetAppts.recordCount GT 0>
   <CFQUERY name="qGetConflict" dbtype="query">
      SELECT *
      FROM qGetAppts
      WHERE <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(sDateTime)#"> >= startTime
      AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(sDateTime)#"> < endTime;
   </CFQUERY>

  <!--- if above QofQ returns any records, then code goes here to tell user they need to change their appointment day or time. This I can do. --->

<cfelse>
  <!--- Code to update database goes here. I know how to do this. --->
</cfif>

Hopefully I've given enough explanation.

Thanks in advance!
0
Comment
Question by:RTayefeh
  • 2
  • 2
4 Comments
 
LVL 15

Expert Comment

by:tim_cs
ID: 16559383
Not sure if this will work or not but give it a try


 SELECT *
 FROM qGetAppts
 WHERE
   startTime between <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(sDateTime)#"> AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(eDateTime)#">
   OR endTime between <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(sDateTime)#"> AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(eDateTime)#">

where sDateTime is the start of the new appointment time and eDateTime is the ending time.  You might also have to switch to just createODBCTime() instead of createODBCDateTime().  
0
 

Author Comment

by:RTayefeh
ID: 16567339
Hi tim_cs,

Getting closer, but it doesn't work in the following situation:
Say an appointment is already scheduled from 11am to 3pm.
I try to schedule an appt from 9am to 11am.  This is a valid time frame, even though the end and start times are equal.
The QofQ returns a record (a conflict), I think because the end and start times are equal.  How can I get it to overlook this?  I ran into this before.  Would I just do a cfif to check for equal times and ignore those?  I'm wondering if there is a better way to accomplish that within the SQL code...

So, if I'm thinking this through correctly, the logic should be:
- it's ok for the new start time to equal an existing end time
- it's ok for the new end time to equal an existing start time

It's getting late and my eyes are starting to cross!

Thanks for your help.

0
 
LVL 15

Accepted Solution

by:
tim_cs earned 2000 total points
ID: 16567742
Maybe this will work.

 SELECT *
 FROM qGetAppts
 WHERE
   ((startTime between <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(sDateTime)#"> AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(eDateTime)#">) AND startTime != <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(eDateTime)#">)
   OR ((endTime between <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(sDateTime)#"> AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(eDateTime)#">) AND endTime != <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(sDateTime)#">)

0
 

Author Comment

by:RTayefeh
ID: 16580626
Thanks a million, tim_cs :)  That helps me out a ton.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question