Posted by John Bell on 06/26/05 23:28

Hi

The query relies on one lunch per shift, what you will need to do is create
a more generic solution using the end time of a non-shift activity as the
start time and the earliest start time of a non-shift activity as the end
time . This will be unioned to the start of shift and the earliest non-shift
start time as the end time and also unioned with the latest non-shift end
time as the start time and the end of shift.

John

<angelasg@comcast.net> wrote in message
news:1119812519.481928.183780@g14g2000cwa.googlegroups.com...
> What I gave was a very simplified example. In actuality you will have
> something like this:
>
> 111, 666, 6/1/2005, LUNCH, 6/1/2005 12:00:00 PM, 6/1/2005 1:00:00 PM,
> 60, 1
> 111, 666, 6/1/2005, BREAK1, 6/1/2005 10:00:00 AM, 6/1/2005 10:15:00
> AM,15, 2
> 111, 666, 6/1/2005, BREAK2, 6/1/2005 3:00:00 PM, 6/1/2005 3:15:00 PM,
> 15, 3
> 111, 666, 6/1/2005, TRAIN, 6/1/2005 8:30:00 AM, 6/1/2005 9:00:00 AM,
> 30, 4
> 112, 666, 6/1/2005, SHIFT, 6/1/2005 8:00:00 AM, 6/1/2005 5:00:00 PM,
> 540, 5
>
>
> Then the result would be:
>
>
> 666, 6/1/2005, SHIFT, 6/1/2005 8:00:00 AM, 6/1/2005 8:30:00 AM, 30, 5
> 666, 6/1/2005, TRAIN, 6/1/2005 8:30:00 AM, 6/1/2005 9:00:00 AM, 30, 4
> 666, 6/1/2005, SHIFT, 6/1/2005 9:00:00 AM, 6/1/2005 10:00:00 AM, 60, 5
> 666, 6/1/2005, BREAK1, 6/1/2005 10:00:00 AM, 6/1/2005 10:15:00 AM, 15,
> 2
> 666, 6/1/2005, SHIFT, 6/1/2005 10:15:00 AM, 6/1/2005 12:00:00 PM, 105,
> 5
> 666, 6/1/2005, LUNCH, 6/1/2005 12:00:00 PM, 6/1/2005 1:00:00 PM, 60, 1
> 666, 6/1/2005, SHIFT, 6/1/2005 1:00:00 PM, 6/1/2005 3:00:00 PM, 120, 5
> 666, 6/1/2005, BREAK2, 6/1/2005 3:00:00 PM, 6/1/2005 3:15:00 PM, 15, 3
> 666, 6/1/2005, SHIFT, 6/1/2005 3:15:00 PM, 6/1/2005 5:00:00 PM, 105, 5
>
> Will the query still work?
>
> Thanks.
>

• Next in forum: connectionopen (SeCDoClientHandshake().]SSL Security error.
• Prev in forum: Re: Parsing a SQL Statement as a parameter and executing it
• Thread view: Re: Need help creating query

[Reply to this message]