Solved

Date Comparision Help Needed!

Posted on 2004-10-19
6
158 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
URL redirect 4 51
cloud web Service looking for a home... 3 100
JVM encoding. How to change encoding. 27 85
.dwt files not viewable in browser - why? 2 75
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 …
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now