Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1961

Re: can't make left join correctly

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

can't make left join correctly "M.G." <michael@gurfinkel.us> - 2016-05-13 18:03 -0700
  Re: can't make left join correctly Erland Sommarskog <esquel@sommarskog.se> - 2016-05-14 11:59 +0200
    Re: can't make left join correctly "M.G." <michael@gurfinkel.us> - 2016-05-16 13:01 -0700

csiph-web