From: esquel@sommarskog.se   
      
   M.G. (michael@gurfinkel.us) writes:   
   > I have a table that reflects events for customers - a customer has   
   > different event types registered along with event time.   
   >...   
   > 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   
   >   
      
   Here is a solution:   
      
    WITH intervals AS (   
       SELECT a.CUST_ID, a.ETIME, b.ENDED   
       FROM   @T a   
       OUTER  APPLY (SELECT MIN(b.ETIME) AS ENDED   
                     FROM   @T b   
                     WHERE  b.CUST_ID = a.CUST_ID   
                       AND  b.ETIME   > a.ETIME   
                       AND  b.EVENT_ID = 2) AS b   
       WHERE  a.EVENT_ID = 1   
   )   
   SELECT CUST_ID, MIN(ETIME) AS STARTED, ENDED   
   FROM   intervals   
   GROUP  BY CUST_ID, ENDED   
   ORDER  BY CUST_ID, STARTED   
      
      
   The MIN(ETIME) is there for the case there are multiple rows with EVENT_ID = 1   
   after each other.   
      
      
   --   
   Erland Sommarskog, Stockholm, esquel@sommarskog.se   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)