asked on

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!

SOLUTION

73Spyder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RTayefeh

ASKER

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?


<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>


<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>

73Spyder

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.

RTayefeh

ASKER

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 ?)

RTayefeh

ASKER

Still hoping for help on this one - if I can give further clarification let me know.

ASKER CERTIFIED SOLUTION

usachrisk1983

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RTayefeh

ASKER

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>

usachrisk1983

Glad it all worked out! :)