f



can't make left join correctly

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.
0
M
5/14/2016 1:03:34 AM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

2 Replies
437 Views

Similar Articles

[PageSpeed] 3

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
0
Erland
5/14/2016 9:59:18 AM
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.
0
M
5/16/2016 8:01:14 PM
Reply: