the dates are broken up like Start_Day, Start_Month, Start_Year,
Start_Time, Start_AMPM and the same for the End info.
1) So there is a bit of overlapping in some cases, where Dr. A may be
on call from 12/12/2003 - 12/14/2003 at 7am
And Dr. B may be on call from 12/14/2003 at 7am until 12/20/2003 at
4pm.
I have a page that outputs the schedule for the month in a calendar
looking format.
So, in trying to output the correct names in the boxes, I am running
the following query:
<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="#DateFormat(ThisDay,
"D")# ">BETWEEN START_DAY AND END_DAY
AND
<cfqueryparam cfsqltype="cf_sql_integer"
value="#DateFormat(ThisCalMonth, "M")# "> BETWEEN START_MONTH AND
END_MONTH
</cfquery>
This is working ALMOST correctly. But I am not getting any overlaps.
Instead, it's pushing forward a day.
So if Dr. A ENDS her shift at 7am on 12/12 and Dr. B BEGINS his shift
at 7am on 12/12, it's taking Dr.A and extending her to 12/13.
2) I only want the start time to show up if it's the first day in the
shift and I only want the end time to show if it's the LAST day in the
shift. How can I get at this info if I am using a range?
Should I put the query results into arrays?
I tried using the currentRow attribute of the above, but then realized
that won't work because of the date range part. One row could be
compensating for 10 days.
Any help is 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]
