[EMAIL PROTECTED] 12/9/2003 3:40:17 PM >>>
> Candace Cottrell wrote:
>
>>I am building an on-call scheduling app for doctors. In the database,
>>the dates are broken up like Start_Day, Start_Month, Start_Year,
>>Start_Time, Start_AMPM and the same for the End info.
>
> I would highly recommend combining these fields together in one
> timestamp field. You could even do so without touching anything
> in the UI by just adding a Start_Timestamp field and recombining
> the timestamp in a trigger on the fly.
> ------
> What is the advantage of using a timestamp vs. a datetime in this scenario?
I have no idea what a datetime exactly is in MS SQL Server, so I
just use the stand SQL terminology:
"The data types DATE, time, and timestamp are collectively
referred to as datetime types."
BTW, there is an advantage in using a timestamp with time zone in
case of DST changes.
> I am also thinking I should do an insert for every day as opposed to the range. Do you see an advantage in doing this?
No.
> In addition, I am a bit worried about the secretaries who have to enter so many records in the following scenario:
>
> Dr. A 12/12 7am - 12/14 7am
> Dr. B 12/14 7am - 12/15 7am
> Dr. A 12/15 7am - 12/17 7am
>
> .instead of being able to pick dates from a calendar popup or something. I watched one of them do it and it was time consuming.
>
> At the same time, I am afraid to take out the range feature, becuase in some cases one doctor is on-call for 2 months. So it's much easier for them to type 12/1/2003 and 2/3/2003 instead of picking 60 days.
You can always provide both ways :-)
What I am refering to is not visible in the UI at all. All you
have to do is define a BEFORE INSERT trigger that combines the
information from Start_Day, Start_Month, Start_Year, Start_Hour
and Start_Minute together into one timestamp field. Then you can
query on that timestamp field without having to provide separate
conditions for all the different fields.
>>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.
>
> Does this really overlap?
> ---
> I guess for an hour it does...
> ---
Really?
>>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.
>
> So do you want both Dr. A and Dr. B to show on 12/12? Or just Dr. B?
> ---
> Actually, they should both be listed.
> Something like:
>
> Dr. A. End: 7am
> Dr. B Start:7am
That is doable. And we shouldn't even need to do a self-join :-)
<cfquery name="getByDay" datasource="#request.DSN#">
(
SELECT
DOC_FIRST_NAME,
DOC_LAST_NAME,
START_MONTH AS shiftmonth,
START_DAY as shiftday,
START_HOUR as shifthour,
START_AMPM as shiftampm,
'start' AS shifttype
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
UNION
SELECT
DOC_FIRST_NAME,
DOC_LAST_NAME,
END_MONTH AS shiftmonth,
END_DAY AS shiftday,
END_HOUR AS shifthour,
END_AMPM AS shiftampm,
'end' AS shifttype
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
)
ORDER BY shiftmonth, shiftday, shiftampm, shifthour, shifttype DESC
</cfquery>
This will give you all the starts and ends of shifts neatly
ordered for display. Just loop over them and put them in your
calendar.
Jochem
--
When you don't want to be surprised by the revolution
organize one yourself
- Loesje
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
