Solved

Date Comparision Help Needed!

Posted on 2004-10-19
6
163 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Website Test Question 6 128
Asp.net plan security, reliable, stable 2 126
Autoresponder for Whole Domain in Plesk/Cpanel 2 86
AWS New EC2 Instance and EBS Storage 2 85
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…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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