Judging from the quality of your postings in the past, I am sure you can modify it to your own needs! As for the discussion regarding separating your dates into separate fields… hey… if I have learned anything in my experience in computer science, its that there is more than one way to do a job, and sometimes the less obvious one is the best one, even when everyone thinks Its crazy. They killed Galileo for saying the earth revolved around the sun! ;)
Here is the code. I stored all my date fields as numeric to increase speed, and I don’t forget about to change the arguments into cfqueryparams, etc. But I am sure you know what to do. If you have any questions about the code let me know! This code works great for checking overlap withi a single date... ie does 13th of September 2003 over lap a ascheduled event... you could easily integrate overlapping with date ranges if you change the operators <= or >= to the BETWEEN sql operator, like I did in the <!---ALTERNATIVE QUERY FOR DATE RANGES --->. I didn’t test this query, but I am pretty sure the logic is correct. Let me know how it worked out for you.
Gabriel
<!---
----------------------------------------------------------------
---------------function description : GetEventsThisDay --------
----------------------------------------------------------------
This function returns a query of events for a given day.
Argument list
CurrentMonth : The month for which we need the event
CurrentYear : The Year for which we need the event
CurrentDay : The day for which we need the event
----------------------------------------------------------------
Author : Gabriel Robichaud
Date : August 7th 2003
________________________________________________________________
--->
<cffunction name="getEventsThisDay" returntype="query">
<cfargument name="CurrentMonth" required="true" type="numeric">
<cfargument name="CurrentYear" required="true" type="numeric">
<cfargument name="CurrentDay" required="true" type="numeric">
<cfquery datasource="#CAB#" username="#username#" password="#password#" name="GET_EVENTS"> SELECT * FROM EVENTS
where EVENT_START_YEAR <= #Arguments.currentYear#
and event_start_month <= #Arguments.currentMonth#
and event_start_day <= #Arguments.currentDay#
and event_end_year >= #Arguments.currentYear#
and event_end_month >= #Arguments.currentMonth#
and event_end_day >= #arguments.currentday#
</cfquery>
<!---ALTERNATIVE QUERY FOR DATE RANGES
<cfquery datasource="#CAB#" username="#username#" password="#password#" name="GET_EVENTS"> SELECT * FROM EVENTS
where (EVENT_START_YEAR between #startyear# and #end_year#
and event_start_month between #startmonth# and #end_month#
and event_start_day between #startday# and #end_day#)
OR (event_end_year between #startyear# and #end_year#
and event_end_month between #startmonth# and #end_month#
and event_end_day between #startday# and #end_day#
)
</cfquery>
--->
<cfreturn get_events>
</cffunction>
Cheers,
Gabriel
>Hello list ;)
>
>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]
