Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

SQL statement not working (dates/times)

My application allows people to schedule appointments with test equipment.  The QofQ checks each new appointment against existing ones to see if there are conflicting times.  It works in all but the following scenario:

There is an existing appointment in the database for Tester A, from 9am to 5pm.
If someone tries to schedule a new appointment for Tester A, from 11am to 1pm (or anytime between 9am-5pm) it's allowed.  This should return a conflict, and thus not be allowed.

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

<CFQUERY name="qGetConflict" dbtype="query">
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)#">);
</CFQUERY>

From what I understand in the QofQ, it seems like it should catch an appointment that is scheduled inbetween but it doesn't.  If anyone can see where the error is or tell me what is missing, I would sure appreciate it.

Thanks!
0
RTayefeh
Asked:
RTayefeh
  • 4
  • 2
  • 2
2 Solutions
 
73SpyderCommented:
I have seen the QoQ have a hard time with this

Where ( condition AND condition ) OR  ( condition AND condition )

Does the query work when you just check the start time?

If so, I would use 2 seaperate QoQ's for this.    One to check the starttime and one to check the end time.

0
 
RTayefehAuthor Commented:
Thanks for the response...I split out the QofQs as you suggested, but I get the same results.  I did a check to see what the record count is for each QofQ and they both returned 0, meaning no conflicting times were found.  Any other ideas?

<!--- QofQ to see if there are conflicting appointments with start time --->
<CFQUERY name="qGetConflict1" dbtype="query">
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)#">);
</CFQUERY>

<!--- QofQ to see if there are conflicting appointments with end time --->
<CFQUERY name="qGetConflict2" dbtype="query">
SELECT *
FROM qGetAppts
WHERE
((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)#">);
</CFQUERY>
0
 
73SpyderCommented:
Are you sure that the values in the database are the same type as the createODBCDateTime  ?  Maybe there is a conflict with the data types.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
RTayefehAuthor Commented:
Yes, they're the same type.  These QofQs work for all other scenarios, exept the one I listed in my first post (ie: There is an existing appointment in the database for Tester A, from 9am to 5pm.  You can schedule an appt from 7am-9am and from 5pm-9pm, but you can't schedule from 3pm-6pm for example, because that's a conflict.  The queries catch that and all other conflicts, but they don't catch it when you try to schedule one from 11am-1pm.)

If there was a conflict with datatypes, I would suspect that it would affect the other scenarios as well (correct ?)
0
 
RTayefehAuthor Commented:
Still hoping for help on this one - if I can give further clarification let me know.
0
 
usachrisk1983Commented:
The problem is in your logic.  From what I read, your saying (in English) in your SQL:

If StartTimeInDatabase is Between an NewStartTime and NewEndTime, Conflict.
If EndTimeInDatabase is Between an NewStartTime and NewEndTime, Conflict.

Assume that your only record is a StartTime of 9am and an EndTime of 9pm.

Now, when you check for a conflict in your SQL for 11am to 1pm, it looks like you're saying:

If 9am is Between 11am to 1pm, conflict (this would return FALSE).
If 5pm is Between 11am to 1pm, conflict (this would return FALSE).

So you're getting no records.  Your query should look more like this:

<CFQUERY name="qGetConflict" dbtype="query">
SELECT * FROM qGetAppts WHERE
#sStartTime# between startTime and EndTime
or #sEndTime# between startTime and EndTime
</cfquery>

Now you're saying:  

If NewStartTime is Between an Existing DatabaseStartTime and Existing DatabaseEndTime, Conflict.
If NewEndTime is Between an Existing DatabaseStartTime and Existing DatabaseEndTime, Conflict.

So when you query for 11am to 1pm, you're saying:

If 11am is Between 9am and 5pm, conflict (TRUE!)
If 1pm is Between 9am and 5pm, conflict (TRUE!)

So you should now get a record.

PS - I removed your CFQUERYPARAMS for clarity in the query above, USE THEM, they IMPORTANT! :)  I usually test without first, then put them in and test again.
0
 
RTayefehAuthor Commented:
Hi usachrisk1983,

Your suggestion fixed the problem with scheduling an appointment in the middle of an existing appointment, but it allowed scheduling an appointment over the top of an existing one (ie: existing appointment for 11am-2pm, try to schedule appt for 9am to 5pm and it's allowed - this should be a conflict).

So...I ended up using my original query and then did a second QofQ with your suggestion, then I ran them both through a cfif and now it all works!  I was hoping to get it done in one QofQ but I guess, whatever works.  Thank you for your help.  I split the points because 73Spyder gave me the idea of splitting out the QofQs and you gave me the solution to the scheduling conflicts.

Below are the QofQs if you're interested (they're really just inverses of each other):

<CFQUERY name="qGetConflict" dbtype="query">
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)#">);
</CFQUERY>

<CFQUERY name="qGetConflict2" dbtype="query">
SELECT *
FROM qGetAppts
WHERE
((<cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(sDateTime)#"> between startTime AND endTime) AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(sDateTime)#"> != endTime)
OR ((<cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(eDateTime)#"> between startTime AND endTime) AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(eDateTime)#"> != startTime);
</CFQUERY>
0
 
usachrisk1983Commented:
Glad it all worked out! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now