Suppose we call the user-supplied range UserStart - UserEnd and the range in the DB DBStart - DBEnd. There are 6 ways by which one date range relates to the other: (1) UserStart UserEnd DBStart DBEnd (No conflict) (2) DBStart DBEnd UserStart UserEnd (No conflict) (3) UserStart DBStart UserEnd DBEnd (Conflict) (4) DBStart UserStart DBEnd UserEnd (Conflict) (5) UserStart DBStart DBEnd UserEnd (Conflict) (6) DBStart UserStart UserEnd DBEnd (Conflict)
Now, if we write the query to detect cases 1 and 2 for no conflict, then reverse the logic, we get the conflict situations. So here goes (off the top of my head): <CFQUERY NAME="ConflictDetect" ...> SELECT COUNT(*) AS Conflicts FROM mytable WHERE NOT (DBStart >= #UserEnd# OR DBEnd <= #UserStart#) </CFQUERY> <CFIF ConflictDetect.Conflicts GT 0> <CFSET Conflict = 1> <CFELSE> <CFSET Conflict = 0> </CFIF> Regards: Ayudh +----------------------------------------------------------------+ | SOAP is the glue! Hook up your server directly to your bank. | | Connect to VeriPay xServ, the Australian Payments Web Service. | | Reliable, Secure, FAST: http://www.xilo.com/xserv | +----------------------------------------------------------------+ ----- Original Message ----- From: "Ricardo Russon" <[EMAIL PROTECTED]> Newsgroups: cfaussie To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Tuesday, June 01, 2004 6:36 PM Subject: [cfaussie] Date Selection > Hi guys, > > I need to find out if a date range entered overlaps a date range > currently in the database. > the database currently stores the date time for start and finish of the > range, and the user > enters a desired start and finish. > > something like > > if the database contains > > startDate finishDate > 3/06/2004 10:00:00 AM 3/06/2004 11:00 AM > > and the user enters > startDate finishDate > 3/06/2004 10:00:00 AM 3/06/2004 10:30 AM > > I need to alert the user that there is an overlap in the data. > > I want to do this in the SQL, but i am stumped on how. :( > Does anyone have any idea. I am using MySQL 4.1.1 > > Thanks > Ricardo. > > --- > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > To unsubscribe send a blank email to [EMAIL PROTECTED] > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
