From: michael@gurfinkel.us   
      
   On Saturday, May 14, 2016 at 2:59:23 AM UTC-7, Erland Sommarskog wrote:   
   > 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   
      
   Thank you very much, works perfect.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)