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

Re: can't make left join correctly

Path	csiph.com!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: can't make left join correctly
Date	Sat, 14 May 2016 11:59:18 +0200
Organization	Erland Sommarskog
Lines	32
Message-ID	<XnsA60879F458637Yazorman@127.0.0.1> (permalink)
References	<c597bee5-a9d4-4fea-8995-c660fe6b7145@googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx02.eternal-september.org; posting-host="390d8ebee780cd6f2dba587f72f23b08"; logging-data="21254"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+nJkGRpLhrs9vfm7F8Kpje"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:/9Du3prPgRGFmLWOl+hMYN7p1Nw=
Xref	csiph.com comp.databases.ms-sqlserver:1961

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