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

Re: can't make left join correctly

X-Received	by 10.36.76.211 with SMTP id a202mr13600998itb.2.1463428874769; Mon, 16 May 2016 13:01:14 -0700 (PDT)
X-Received	by 10.157.7.51 with SMTP id 48mr342596ote.2.1463428874737; Mon, 16 May 2016 13:01:14 -0700 (PDT)
Path	csiph.com!feeder.erje.net!2.us.feeder.erje.net!news.glorb.com!i5no9057370ige.0!news-out.google.com!uv8ni144igb.0!nntp.google.com!i5no9057359ige.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Mon, 16 May 2016 13:01:14 -0700 (PDT)
In-Reply-To	<XnsA60879F458637Yazorman@127.0.0.1>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=71.95.163.250; posting-account=99cyNgoAAAA03l-zLDrnoY7TEbs-AvM9
NNTP-Posting-Host	71.95.163.250
References	<c597bee5-a9d4-4fea-8995-c660fe6b7145@googlegroups.com> <XnsA60879F458637Yazorman@127.0.0.1>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<a1122108-3a62-4233-883a-76d4a9a57032@googlegroups.com> (permalink)
Subject	Re: can't make left join correctly
From	"M.G." <michael@gurfinkel.us>
Injection-Date	Mon, 16 May 2016 20:01:14 +0000
Content-Type	text/plain; charset=UTF-8
Xref	csiph.com comp.databases.ms-sqlserver:1962

Show key headers only | 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