Need help creating query

[angelasg]

I am working with employee schedules. Each schedule is comprised of
segments (shift, lunch, break, training, etc.) that have rankings.
Each record has the employee id, the date the shift starts, the start
and end time of each segment, the duration,the segment type and its
rank. The start and end times of the schedules can overlap, but the
segment that has the higher rank takes precedence.

As a simple example, an employee working 8a-5p will have two records.
The shift segment from 8a-5p and a lunch segment from 12p-1p. The
lunch ranks higher so, even though the shift goes from 8a-5p, from
12p-1p, the lunch takes precedence.

What I'm trying to do is build a query that will collapse the segments.
So given the example above, I will now have three records: shift
8a-12p, lunch 12p-1p, shift 1p-5p.

I have been racking my brain but I just can't figure it out.
Ultimately, a programmer in my office will display this in a bar chart
format with each segment type having its own color. I'm trying to do as
much of the work in SQL so he doesn't have to calculate this stuff in
the code.

Any help would be appreciated.

Thanks.
Angela

[John Bell]

Hi

Posting DDL and example data removes any ambiguity that a long description
may contain. http://www.aspfaq.com/etiquett*e.asp?id=5006

It sounds like you have a repeating groups in your records say

CREATE TABLE Schedules ( employeeid int not null,
shiftstartdate datetime not null,
s1start datetime,
s1end datetime,
s1type int,
s1rank int,
s2start datetime,
s2end datetime,
s2type int,
s2rank int,
s3start datetime,
s3end datetime,
s3type int,
s3rank int )

As rank is an attribute of the segment type it may not be necessary to store
this as type can be looked up.

The to get separate records you could try something like:

SELECT employeeid,
shiftstartdate,
s1start, AS SegmentStart,
s1end, AS SegmentEnd
s1type AS SegmentType,
s1rank AS SegmentRank
UNION ALL
SELECT employeeid,
shiftstartdate,
s2start,
s2end,
s2type,
s2rank
WHERE s2start IS NOT NULL
UNION ALL
SELECT employeeid,
shiftstartdate,
s3start,
s3end,
s3type,
s3rank
WHERE s3start IS NOT NULL
ORDER BY SegmentRank

John

<an******@comcast.net> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

I am working with employee schedules. Each schedule is comprised of
segments (shift, lunch, break, training, etc.) that have rankings.
Each record has the employee id, the date the shift starts, the start
and end time of each segment, the duration,the segment type and its
rank. The start and end times of the schedules can overlap, but the
segment that has the higher rank takes precedence.

As a simple example, an employee working 8a-5p will have two records.
The shift segment from 8a-5p and a lunch segment from 12p-1p. The
lunch ranks higher so, even though the shift goes from 8a-5p, from
12p-1p, the lunch takes precedence.

What I'm trying to do is build a query that will collapse the segments.
So given the example above, I will now have three records: shift
8a-12p, lunch 12p-1p, shift 1p-5p.

I have been racking my brain but I just can't figure it out.
Ultimately, a programmer in my office will display this in a bar chart
format with each segment type having its own color. I'm trying to do as
much of the work in SQL so he doesn't have to calculate this stuff in
the code.

Any help would be appreciated.

Thanks.
Angela

[angelasg]

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.

[John Bell]

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
<an******@comcast.net> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.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.

[angelasg]

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.

[John Bell]

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

<an******@comcast.net> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.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.