Solved

SQL statement not working (dates/times)

Posted on 2006-06-21
8
282 Views
Last Modified: 2013-12-24
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
Comment
Question by:RTayefeh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 9

Assisted Solution

by:73Spyder
73Spyder earned 100 total points
ID: 16954687
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
 

Author Comment

by:RTayefeh
ID: 16955912
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
 
LVL 9

Expert Comment

by:73Spyder
ID: 16958640
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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Author Comment

by:RTayefeh
ID: 16961624
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
 

Author Comment

by:RTayefeh
ID: 16986264
Still hoping for help on this one - if I can give further clarification let me know.
0
 
LVL 13

Accepted Solution

by:
usachrisk1983 earned 400 total points
ID: 17022256
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
 

Author Comment

by:RTayefeh
ID: 17046726
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
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 17050157
Glad it all worked out! :)
0

Featured Post

Supports up to 4K resolution!

The VS192 2-Port 4K DisplayPort Splitter is perfect for anyone who needs to send one source of DisplayPort high definition video to two or four DisplayPort displays. The VS192 can split and also expand DisplayPort audio/video signal on two or four DisplayPort monitors.

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

630 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