SQL 2000 under W2K
I don't know how to deal with reptetitive results from self-join, any
pointers would be appreciated.

I have an order table

CREATE TABLE [dbo].[XXX](
[EMP_ID] [int] NOT NULL,
[DRV_ID] [int] NOT NULL,
[ORDER_ID] [int] NOT NULL,
[SHIFT_ID] [int] NULL,
[ORDER_TIME] [datetime] NULL,
[SHIP_TIME] [datetime] NULL
)

The data is:

SELECT 1018, 9017, 34191793, 1603075,'2009-09-17 08:10','2009-09-17
08:27' UNION
SELECT 5064, 9045, 34191815, 1603078,'2009-09-17 08:38','2009-09-17
09:12' UNION
SELECT 5063, 9011, 34191822, 1603073,'2009-09-17 08:02','2009-09-17
08:28' UNION
SELECT 3007, 9030, 34191784, 1603077,'2009-09-17 08:51','2009-09-17
09:08' UNION
SELECT 3007, 9030, 34191785, 1603077,'2009-09-17 08:51','2009-09-17
09:08' UNION
SELECT 3007, 9030, 34191786, 1603077,'2009-09-17 08:51','2009-09-17
09:08'


I need to show orders by same employee that were started before
previous order has been shipped, so the order_time is same or after
previous order_time, but before previous ship_date

My query :
SELECT t1.EMP_ID
, t1.DRV_ID
, t1.SHIFT_ID
, t1.ORDER_ID AS ORDid1
, t1.ORDER_TIME AS OTime1
, t1.SHIP_TIME AS STime1
, t2.ORDER_ID AS ORDid2
, t2.ORDER_TIME AS OTime2
, t2.SHIP_TIME AS STime2
FROM XXX t1
JOIN XXX t2 ON t1.EMP_ID=t2.EMP_ID
WHERE t1.SHIFT_ID = t2.SHIFT_ID
AND t1.ORDER_ID <> t2.ORDER_ID
AND t2.ORDER_TIME >= t1.ORDER_TIME AND t2.ORDER_TIME <
t1.SHIP_TIME

Results have
EMP_ID|DRV_ID|SHIFT_ID|ORDid1|OTime1|STime1|ORDid2 |OTime2|STime2
3007|9030|1603077|34191784|2009-09-17 08:51|2009-09-17 09:08|34191785|
2009-09-17 08:51:00.000|2009-09-17 09:08:00.000
3007|9030|1603077|34191784|2009-09-17 08:51|2009-09-17 09:08|34191786|
2009-09-17 08:51:00.000|2009-09-17 09:08:00.000
3007|9030|1603077|34191785|2009-09-17 08:51|2009-09-17 09:08|34191784|
2009-09-17 08:51:00.000|2009-09-17 09:08:00.000
3007|9030|1603077|34191785|2009-09-17 08:51|2009-09-17 09:08|34191786|
2009-09-17 08:51:00.000|2009-09-17 09:08:00.000
3007|9030|1603077|34191786|2009-09-17 08:51|2009-09-17 09:08|34191784|
2009-09-17 08:51:00.000|2009-09-17 09:08:00.000
3007|9030|1603077|34191786|2009-09-17 08:51|2009-09-17 09:08|34191785|
2009-09-17 08:51:00.000|2009-09-17 09:08:00.000

I need to see just two records for emp_id 3007, ids 34191785,
34191786, as these two orders started when id 34191784 started and not
done yet.
Hope I made myself clear.

Not really; the DDL and specs are a bit vague.

1) Is the key to the table (emp_id, drv_id, order_id) or something
else?

2) Does a NULL order_time mean that it has not been ordered yet?

3) Does a NULL ship_time mean that it has not been shipped yet? I see
you allow something to shipped before it is ordered.

4) What does a NULL shift_id mean? How do we compare it with another
known or NULL shift_id?

The following will work, but it returns an extra row because ids 34191785 and 34191786 also satisfy your requirements:

SELECT t1.EMP_ID
, t1.DRV_ID
, t1.SHIFT_ID
, t1.ORDER_ID AS ORDid1
, t1.ORDER_TIME AS OTime1
, t1.SHIP_TIME AS STime1
, t2.ORDER_ID AS ORDid2
, t2.ORDER_TIME AS OTime2
, t2.SHIP_TIME AS STime2
FROM XXX AS t1
JOIN XXX AS t2
ON t1.EMP_ID = t2.EMP_ID
WHERE t1.SHIFT_ID = t2.SHIFT_ID
AND t1.ORDER_ID < t2.ORDER_ID
AND t2.ORDER_TIME >= t1.ORDER_TIME
AND t2.ORDER_TIME < t1.SHIP_TIME;

--
Plamen Ratchev
http://www.SQLStudio.com