Re: difficult JOIN

On Wed, Jan 26, 2005 at 11:22:48AM +0100, Thomas Chille wrote:
> > More important, does it even make sense? What if an employee ends up not
> > working at all for one of his/her planned times? Every record after that
> > would be completely skewed. Wouldn't it make much more sense to either
> > assign an ID to each record in the plan table, and correlate records in
> > the work table using that ID, or correlate based on begin and end time?
>
> This report should not be the final thing. It schould only present the
> matched times on an easy way and the tourleader can than edit the
> worktimes manually. He have to do this after every tour because the
> employees are often not using the timerecording unit correct (i'm
> talking about restaurants).

One thing that may help is to do an inner join on (id_tour,
id_employee) and then match the following:

work.starttime < plan.endtime and plan.starttime < work.endtime

This will join then and give a row whenever there is an overlap between
the two records. Ofcourse, if there is no overlap the record doesn't
appear at all, but an outer join might work here...

Ah no, IIRC postgresql only supports outer joins on merge join able
conditions so that wont work. But the above should be a start...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.