Solved

SQL statement not working (dates/times)

Posted on 2006-06-21
8
275 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
  • 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now