I have a db table in which I store Start and End Dates called
ONCALL_SHIFTS. These are ranges.
For example
Start_Month 12
Start_Day 1
Start_Year 2003
Start_Time 8
End_Month 12
End_Day 15
End_Year 2003
End_Time 8
There are also two shift types: Backup and Primary
So I have a form where the schedulers can add new shifts. It basically
asks them for the start and end info, the doctor's name, and what the
shift type is.
Now, what I want to check for is that there are no overlaps in the
shifts, but only if the shift_types are the same; meaning, it's ok to
have 2 doctors scheduled for 12/1/2003-12/15/2003 as long as the
shift_types are different (backup and primary). It's also ok if one
doctor is scheduled for both shift types (ex. Dr. X is both backup and
primary on this date range).
I know I need to pull a query that brings back the records that would
be overlapped. And if that recordset is empty, proceed with the insert.
Otherwise, give the user a message that there is an overlap and take
them back to the form.
The problems are:
1) I can't figure out what should be in the WHERE clause.
2) Have I screwed myself by chopping up the dates?
Here's the idea:
<cfset formDate = "form.Start_Month/form.Start_Day/form.Start_Year">
<cfset formTime = "form.Start_Hour">
<cfquery name="qrycheckForDupes" datasource="#request.DSN#">
SELECT
Shift_ID
Start_Day,
Start_Month,
Start_Hour,
Start_Year
FROM
ONCALL_SHIFTS
WHERE
?????
</cfquery>
<cfif qrycheckForDupes.recorset NEQ 0>
<cfquery name="InsertSHIFT" datasource="#request.DSN#">
INSERT INTO
ONCALL_SHIFTS
(Start_Day,
Start_Month,
Start_Hour,
Start_Year,
Start_AMPM,
Schedule_Type_ID,
End_Day,
End_Month,
End_Hour,
End_Year,
End_AMPM,
Doctor_ID,
Division_ID
)
VALUES
(
#Form.Start_Day#,
#Form.Start_Month#,
#Form.Start_Hour#,
#Form.Start_Year#,
'#Form.Start_AMPM#',
#Form.Schedule_Type_ID#,
#Form.End_Day#,
#Form.End_Month#,
#Form.End_Hour#,
#Form.End_Year#,
'#Form.End_AMPM#',
#Form.Doctor_ID#,
#URL.DIVISIONID#
)
</cfquery>
<cfelse>
_javascript_... go back.. blah blah
</cfif>
For those of who who have encountered such a problem, what were your
experiences??
Any help would be greatly appreciated :)
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
