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

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

can't make left join correctly

Show all headers | 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