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

can't make left join correctly

X-Received	by 10.140.102.3 with SMTP id v3mr12206542qge.30.1463187815215; Fri, 13 May 2016 18:03:35 -0700 (PDT)
X-Received	by 10.157.32.137 with SMTP id x9mr228941ota.11.1463187815051; Fri, 13 May 2016 18:03:35 -0700 (PDT)
Path	csiph.com!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!peer02.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!11no4237906qgt.0!news-out.google.com!k10ni12igv.0!nntp.google.com!i5no8299443ige.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Fri, 13 May 2016 18:03:34 -0700 (PDT)
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
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<c597bee5-a9d4-4fea-8995-c660fe6b7145@googlegroups.com> (permalink)
Subject	can't make left join correctly
From	"M.G." <michael@gurfinkel.us>
Injection-Date	Sat, 14 May 2016 01:03:35 +0000
Content-Type	text/plain; charset=UTF-8
X-Received-Bytes	2602
X-Received-Body-CRC	4191863201
Xref	csiph.com comp.databases.ms-sqlserver:1960

Show key headers only | View raw

I have a table that reflects events for customers - a customer has different event types registered along with event time.
Here is my setup:
declare	@T table (
	CUST_ID		int	not null
,	EVENT_ID	int	not null
,	ETIME		time not null
);
insert into @T(CUST_ID, EVENT_ID, ETIME) values
(123,   1,      '05:00'),
(123,   2,      '05:05'),
(123,   3,      '05:15'),
(123,   4,      '05:30'),

(234,   1,      '06:00'),
(234,   7,      '06:01'),
(234,   8,      '06:02'),


(345,   1,      '08:15'),
(345,   2,      '08:20'),
(345,   1,      '08:42'),
(345,   2,      '09:05');

event_id = 1 means process started and event_id = 2 means process ended

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

My desired output:
CUST_ID	STARTED	ENDED
123	5:00	5:05
234	6:00	NULL
345	8:15	8:20
345	8:42	9:05

---
My solution:
select 
  t1.CUST_ID
, convert(char(5),t1.ETIME,8)	[STARTED]
, convert(char(5),t2.ETIME,8)	[ENDED]

from	
		@T	t1
left join	@T	t2		on	t1.CUST_ID = t2.CUST_ID 
						and t2.EVENT_ID = 2

where
	t1.EVENT_ID = 1

	and t2.ETIME =	(	select MIN(t.ETIME) 
				from @T t 
				where  t.CUST_ID = t1.CUST_ID
				and	t.EVENT_ID = 2
				and	t.ETIME > t1.ETIME
			)
;

I am getting:
CUST_ID	STARTED	ENDED
123	05:00	05:05
345	08:15	08:20
345	08:42	09:05

see cust_id 234 is missing.

If I omit "and" part of "where" clause I am getting my cust_id 234, but then my cust_id 345 shows extra wrong lines.

Any idea how to address this situation is appreciated.

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