Solved

SQL statement not working (dates/times)

Posted on 2006-06-21
8
278 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
Scale it in WD Gold

With up to ten times the workload capacity of desktop drives, WD Gold hard drives employ advanced technology to deliver among the best in reliability, capacity, power efficiency and performance.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

17 Experts available now in Live!

Get 1:1 Help Now