Solved

# Date Comparision Help Needed!

Posted on 2004-10-19
162 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
Question by:theamzngq
• 3
• 3
6 Comments

LVL 35

Accepted Solution

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

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

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

Does it work how you need it to?
0

LVL 2

Author Comment

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

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

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

Question has a verified solution.

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

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

#### 777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.