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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)