|
|
Posted by John Bell on 06/26/05 19:19
Hi
Posting DDL and example data removes any ambiguity that a long description
may contain. http://www.aspfaq.com/etiquette.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
<angelasg@comcast.net> wrote in message
news:1119798614.340884.239360@g44g2000cwa.googlegroups.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
>
Navigation:
[Reply to this message]
|