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

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

Re: can't make left join correctly

Show all headers | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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