> 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.
> 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?
> 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?
> 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.
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]
