|
|
Posted by John Bell on 06/26/05 20:48
Hi
This may be overly simplified but, if there is always the pair of records:
SELECT S.EMP_ID,
S.SHIFTDATE,
S.SEG_CODE,
S.START_MOMENT,
L.START_MOMENT AS STOP_MOMENT,
DATEDIFF ( MI, S.START_MOMENT, L.START_MOMENT ) AS DURATION,
S.RANK
FROM SCHEDULES S
JOIN SCHEDULES L ON S.EMP_ID = L.EMP_ID
AND S.SHIFTDATE = L.SHIFTDATE
AND S.SEG_CODE = 'SHIFT'
AND L.SEG_CODE = 'LUNCH'
UNION
SELECT L.EMP_ID,
L.SHIFTDATE,
L.SEG_CODE,
L.START_MOMENT,
L.STOP_MOMENT,
L.DURATION,
L.RANK
FROM SCHEDULES L
WHERE L.SEG_CODE = 'LUNCH'
UNION ALL SELECT S.EMP_ID,
S.SHIFTDATE,
S.SEG_CODE,
L.STOP_MOMENT AS START_MOMENT,
S.STOP_MOMENT,
DATEDIFF ( MI, L.STOP_MOMENT, S.STOP_MOMENT ) AS DURATION,
S.RANK
FROM SCHEDULES S
JOIN SCHEDULES L ON S.EMP_ID = L.EMP_ID
AND S.SHIFTDATE = L.SHIFTDATE
AND S.SEG_CODE = 'SHIFT'
AND L.SEG_CODE = 'LUNCH'
ORDER BY S.EMP_ID, S.START_MOMENT
John
<angelasg@comcast.net> wrote in message
news:1119805732.060482.114800@g14g2000cwa.googlegroups.com...
> Here is the table structure. I did not create this table. The data
> was exported from our workforce management software.
>
> PRI_INDEX Long Integer,
> EMP_ID Long Integer,
> SHIFTDATE Date/Time,
> SEG_CODE Text,
> START_MOMENT Date/Time,
> STOP_MOMENT Date/Time,
> DURATION Long Integer,
> RANK Long Integer,
>
> Here is sample data of an employee who works 8a-5p on a given day:
>
> 111, 666, 6/1/2005, LUNCH, 6/1/2005 12:00:00 PM, 6/1/2005 1:00:00 PM,
> 60, 1
> 112, 666, 6/1/2005, SHIFT, 6/1/2005 8:00:00 AM, 6/1/2005 5:00:00 PM,
> 540, 2
>
> Here is the result I would like:
>
> 666, 6/1/2005, SHIFT, 6/1/2005 8:00:00 AM, 6/1/2005 12:00:00 PM, 240, 2
> 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 5:00:00 PM, 240, 2
>
> Thanks again.
>
Navigation:
[Reply to this message]
|