Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Date Comparision Help Needed!

Posted on 2004-10-19
6
Medium Priority
?
167 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:David Williamson
[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 2000 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:David Williamson
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
Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

 
LVL 2

Author Comment

by:David Williamson
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:David Williamson
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

Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

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…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

610 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