Solved

Date Comparision Help Needed!

Posted on 2004-10-19
6
165 Views
Last Modified: 2013-12-24
I am making a scheduling app to help book singers on holiday caroling jobs.  I have two tables (how they are set up can be seen here: http://www.wrightengineers.com/mytables.jpg).  The Jobs table has start and end times for each job.  The SingerSchedule table has start and end times of periods of time that singers are NOT available, in other words, where they have previous commitments.  I am having trouble writing code to tell me which singers are available to book which jobs.

So, if I have a singer who is NOT available 9a-5p, I want to be able to find out if they can work a job that is 6p-10p.  Or, if the singer is completely open all day long, there won't be an entry in the singerschedule table since they've only been entering conflicts & previous commitments.  To further complicate things, each job gets a quartet: soprano, tenor, alto, bass.  Only one of each type (S,A,T,B) can be assigned to one job, for a total of four singers.

Any ideas?
0
Comment
Question by:theamzngq
[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
  • 3
  • 3
6 Comments
 
LVL 35

Accepted Solution

by:
mrichmon earned 500 total points
ID: 12352213
Basically you need to write a conflict detection.

It is very easy.

Consider the possibilities

Booked from 2-4

You can have the following situations

12:00 - 1:00 - completely before
1:00 - 2:00 - completely before but touches start
1:00-3:00 overlaps start
1:00-5:00 - overlaps start and end
3:00-4:00 - overlaps end
4:00-5:00 completely after but touches end
5:00-6:00 - completely after
2:30-3:30 - completely within overlap range

And a few others where it touches a begin or end.

Basically you want to know if they are available - i.e. no overlap

This will tell you if there is an overlap:

(assume cloumsn are starttime and endtime and fields are reservationstart and reservationend)

SELECT * FROM yourtable WHERE
starttime <= reservationend AND endtime >= reservationstart

Then something like

<cfif query.RecordCount GT 0>
You have a conflict
<cfelse>
They are available
</cfif>
0
 
LVL 2

Author Comment

by:theamzngq
ID: 12372462
Thank you!  With your help and that of others, I've come up with the following which seems to work well:

<cfquery datasource="llc" name="getSingers">
      SELECT SingerID,FirstName,LastName,VoicePart FROM Singers
      ORDER BY SingerID
</cfquery>

<cfquery datasource="llc" name="getJobTimes">
      SELECT StartDate,EndDate FROM Jobs
      WHERE JobID = #URL.jid#
</cfquery>
<cfoutput>
<b>These are the singers that are available for #DateFormat(getJobTimes.StartDate,'DDD, MMM d, yyyy')# from #TimeFormat(getJobTimes.StartDate)# - #TimeFormat(getJobTimes.EndDate)#</b><br><br>
<table class="jobList">
      <tr>
            <td>
            <b>Name</b>
            </td>
            <td>
            <b>Part</b>
            </td>
            <td>
            <b>Conflict As Entered In Schedule </b>
            </td>
      </tr>
      <tr>
            <cfloop query="getSingers">
                  <cfset Full_Name = getSingers.FirstName&' '&getSingers.LastName>
                  <cfset Voice_Part = getSingers.VoicePart>
                  <cfquery datasource="llc" name="findOpen">
                        SELECT SingerSchID FROM SingerSchedule
                        WHERE SingerSchedule.SingerID = #getSingers.SingerID# AND DATE_FORMAT(noAvailStart,'%Y-%m-%d') = DATE_FORMAT('#getJobTimes.StartDate#','%Y-%m-%d')
                  </cfquery>
                  <cfif findOpen.RecordCount>
                        <cfloop query="findOpen">
                              <cfquery datasource="llc" name="checkConflict">
                                    SELECT SingerID,noAvailStart,noAvailEnd FROM SingerSchedule
                                    WHERE SingerSchID = #findOpen.SingerSchID# AND
                                    (
                                                (DATE_FORMAT(noAvailEnd,'%H:%i:%s') <= DATE_FORMAT('#getJobTimes.StartDate#','%H:%i:%s')) OR
                                                (DATE_FORMAT(noAvailStart,'%H:%i:%s') >= DATE_FORMAT('#getJobTimes.EndDate#','%H:%i:%s'))
                                    )
                              </cfquery>
                              <cfif checkConflict.RecordCount>
                              <td style="text-align:left ">#Full_Name#</td><td>#Voice_Part#</td><td>#TimeFormat(checkConflict.noAvailStart)# - #TimeFormat(checkConflict.noAvailEnd)#</td></tr><tr>
                              </cfif>
                        </cfloop>
                  <cfelse>
                        <td style="text-align:left ">#Full_Name#</td><td>#Voice_Part#</td><td>None</td></tr><tr>
                  </cfif>
            </cfloop>
            </cfoutput>
      </tr>
</table>

What do you think?
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12372508
It really matters what you think :o)

Does it work how you need it to?
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 2

Author Comment

by:theamzngq
ID: 12372524
yes, it does work.  However, I am always looking for ways to write better, faster, and cleaner code.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12373285
The only improvements I would recommend is to use cfqueryparam to parametarize all inputs to the database.

FOr example

<cfquery datasource="llc" name="getJobTimes">
     SELECT StartDate,EndDate FROM Jobs
     WHERE JobID = #URL.jid#
</cfquery>

Becomes (assuming jid is an integer)

<cfquery datasource="llc" name="getJobTimes">
     SELECT StartDate,EndDate FROM Jobs
     WHERE JobID = <cfqueryparam cfsqltype="cf_sql_type" value="#URL.jid#">
</cfquery>
0
 
LVL 2

Author Comment

by:theamzngq
ID: 12373294
good idea.  I usually use that in all my UPDATE and INSERT statements, I didn't actually think about that here.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
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 …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

717 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