Posted by Jared Williams on 10/08/05 11:14

>
> On Mon, April 25, 2005 3:14 pm, Richard Collyer said:
> > This is probably more of a db question but...
>
> Probably.
>
> > I am writing an employee database for work. The majority of it is
> > simple enough however I am stuck on the timetabling. It has
> to be done
> > in PHP as there are 40 plus team leaders whose computers
> are all old
> > and have IE on them as one similarity. Also any changes I
> make to the
> > program are repllicated over those machines instantly.
>
> Hrmmmm.
>
> > We have ~1500 Employees. There are 4 shifts which overlap.
> >
> > My idea of doing this was to have a database with each
> record being a
> > day for a particular employee. Recording ID, Start Time,
> Finish Time
> > in TimeStamp.
>
> Okay.
>
> > So I could then do a search for the number of employees whose shift
> > start or finished in that particular day. If there is a start and a
> > finish left add up the hours, if only a start do start ->
> midnight and
> > if only a finish do midnight -> finish.
> >
> > Is this a sensible way to do it? Or can anyone suggest a
> better tried
> > and tested way.
>
> Doing what, exactly?
>
> I mean, it kinda depends on what you want to *DO* other than
> the search you give as an example...
>
> Which, by the way, I would recommend coding more like this:
>
> 1. Find everybody whose shift STARTED on date (D).
> 2. Subtract their finish time from start time for their hours
> (H) on that date (D).
>
> You then don't have to muck around with midnight and shifts
> that start/end on different days.
>
> You just pretend that their shift happend on the date of the
> start time, and that's the day you associate with that shift,
> no matter when it ended.
>
> This simplifies everything else you're going to do with the
> data/queries immensely.

Just have to make sure DST doesn't cause any problems.

Jared

[Back to original message]