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]
>>> [EMAIL PROTECTED] 12/9/2003 4:10:52 PM >>>
Candace,
I think that Jochem is using postgresql so, timestamp in postgre = datetime
in MSSql2K.
As for the UI, why don't you give them a calendar on one page with check
boxes and have that submit to a second page that shows an entry for start
time and end time of each range (consider a single day by itself as a range
of it's own)?
Steve
-----Original Message-----
From: Candace Cottrell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 09, 2003 4:01 PM
To: CF-Talk
Subject: Re: More date stuff!
Responses inline...
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org <http://www.childrensdayton.org>
[EMAIL PROTECTED]
>>> [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 am also thinking I should do an insert for every day as opposed to the
range. Do you see an advantage in doing this?
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.
------
> 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...
---
> 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
----
> 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?
I think you need to self-join so you can get start and end
timestamps in one field. The and use a case statement to
determine if a schedule event is a start or an end and sort on
that field. If that doesn't make sense, please provide the full
table schema's.
-----
The only way I can see to do this is to have a separate record for each day.
Here's what the OnCall_Schedule table looks like:
http://www.childrensdayton.org/images/oncallSchema.gif
<http://www.childrensdayton.org/images/oncallSchema.gif>
-----
THANKS DEAR!!!
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]
