From: michael@gurfinkel.us   
      
   I have a table that reflects events for customers - a customer has different   
   event types registered along with event time.   
   Here is my setup:   
   declare	@T table (   
   	CUST_ID		int	not null   
   ,	EVENT_ID	int	not null   
   ,	ETIME		time not null   
   );   
   insert into @T(CUST_ID, EVENT_ID, ETIME) values   
   (123,   1,      '05:00'),   
   (123,   2,      '05:05'),   
   (123,   3,      '05:15'),   
   (123,   4,      '05:30'),   
      
   (234,   1,      '06:00'),   
   (234,   7,      '06:01'),   
   (234,   8,      '06:02'),   
      
      
   (345,   1,      '08:15'),   
   (345,   2,      '08:20'),   
   (345,   1,      '08:42'),   
   (345,   2,      '09:05');   
      
   event_id = 1 means process started and event_id = 2 means process ended   
      
   I need to show customers and when their process was started and ended, as you   
   could see not necessarily all are ended and some can be started/ended several   
   times   
      
   My desired output:   
   CUST_ID	STARTED	ENDED   
   123	5:00	5:05   
   234	6:00	NULL   
   345	8:15	8:20   
   345	8:42	9:05   
      
   ---   
   My solution:   
   select   
     t1.CUST_ID   
   , convert(char(5),t1.ETIME,8)	[STARTED]   
   , convert(char(5),t2.ETIME,8)	[ENDED]   
      
   from	   
   		@T	t1   
   left join	@T	t2		on	t1.CUST_ID = t2.CUST_ID   
   						and t2.EVENT_ID = 2   
      
   where   
   	t1.EVENT_ID = 1   
      
   	and t2.ETIME =	(	select MIN(t.ETIME)   
   				from @T t   
   				where  t.CUST_ID = t1.CUST_ID   
   				and	t.EVENT_ID = 2   
   				and	t.ETIME > t1.ETIME   
   			)   
      
      
   I am getting:   
   CUST_ID	STARTED	ENDED   
   123	05:00	05:05   
   345	08:15	08:20   
   345	08:42	09:05   
      
   see cust_id 234 is missing.   
      
   If I omit "and" part of "where" clause I am getting my cust_id 234, but then   
   my cust_id 345 shows extra wrong lines.   
      
   Any idea how to address this situation is appreciated.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)