I am still working on this on-call calendar.
Here's what I am trying to accomplish:
If the day (number) is the first day of the shift and the shift start
hour is not equal to 8, then I'd like to output "ON: 7AM" under the
doctor's name.
Likewise, if it is the LAST day of the shift and the shift END hour is
not equal to 8, then I'd like to output "OFF:7AM" under the doctor's
name.
So here I am using 2 query of queries to determine the first day of the
shift and the last day of the shift:
000000000000000000000000000000000000000000000000000000000000000000
<cfloop from="1" to="#rows#" index="i">
<tr height="15%">
<cfloop from="1" to="7" index="j">
<td align="left" valign="top">
<cfif #page[j][i]# is -1>
<cfelse>
<!-- Outputting the DAY number-->
<h2><cfoutput>#page[j][i]#</cfoutput> </h2>
<span class="bodyCopy">
<cfquery name="getByDay" datasource="#request.DSN#">
SELECT
DOC_FIRST_NAME,
DOC_LAST_NAME,
START_MONTH,
START_DAY,
START_HOUR,
START_AMPM,
END_DAY,
END_MONTH,
END_HOUR,
END_AMPM
FROM
DOCTORS, DIVISIONS, ONCALL_SHIFTS
WHERE
ONCALL_SHIFTS.DIVISION_ID = <cfqueryparam
cfsqltype="cf_sql_integer" value="9">
AND
DIVISIONS.DIVISIONID = ONCALL_SHIFTS.DIVISION_ID
AND
ONCALL_SHIFTS.DOCTOR_ID = DOCTORS.Doc_ID
AND
<cfqueryparam cfsqltype="cf_sql_integer" value="#page[j][i]#
">BETWEEN START_DAY AND END_DAY
AND
<cfqueryparam cfsqltype="cf_sql_integer" value="#URL.M#">
BETWEEN START_MONTH AND END_MONTH
</cfquery>
<!-- Determining the first day of the shift-->
<cfquery dbtype="query" name="getFirstDay" >
SELECT MAX(Start_Day) AS Start_Day
FROM
getByDay
</cfquery>
<!-- Determining the last day of the shift-->
<cfquery dbtype="query" name="getLastDay" >
SELECT MAX(End_Day) AS End_Day
FROM
getByDay
</cfquery>
<!-- Outputting the doctor who is scheduled for this particular
day-->
<cfoutput query="getByDay"><strong>#Doc_Last_name#</strong>
<!-- If this is the first day in the shift-->
<cfif getFirstDay.Start_Day IS '#page[j][i]#'>
<!-- If there is an start time other than the default 8AM-->
<cfif getByDay.START_HOUR NEQ 8>
<br><em>ON: #getByDay.START_HOUR##getByDay.START_AMPM#</em>
</cfif>
</cfif>
<!-- If this is the last day in the shift-->
<cfif getLastDay.End_Day IS '#page[j][i]#'>
<!-- If there is an end time other than the default 8AM-->
<cfif getByDay.END_HOUR NEQ 8>
<em><br>OFF:#getByDay.END_HOUR##getByDay.END_AMPM#</em>
</cfif>
</cfif>
<br>
</cfoutput>
</span>
</cfif>
</td>
</cfloop>
</tr>
</cfloop>
000000000000000000000000000000000000000000000000000000000000000000
The result I am getting from this is what is stumping me.
http://www.childrensdayton.org/onCallFebCalendar.gif
All of the data is correct and the firstDay and LastDay are coming in
correctly. It seems that the case is:
If there is an overlap, the OFF is not showing or it is showing as ON
(example: Feb. 10 and Feb. 19)
If there is no overlap, the OFF shows, but not the ON. (example: Feb. 6
and Feb. 28)
I'm thinking there must be a problem in the order in which I have the
above. I've tried moving the Query and the QOQ's around, but to no
avail. Any help is greatly appreciated, as always!
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]
