1. Forum
  2. Usenet
  3. COMP.DATABASES.MS-SQLSERV

  • can't make left join correctly

    From M.G.@21:1/5 to All on Fri May 13 18:03:34 2016
    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)
  • From Erland Sommarskog@21:1/5 to M.G. on Sat May 14 11:59:18 2016
    M.G. ([email protected]) 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, [email protected]

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From M.G.@21:1/5 to Erland Sommarskog on Mon May 16 13:01:14 2016
    On Saturday, May 14, 2016 at 2:59:23 AM UTC-7, Erland Sommarskog wrote:
    M.G. ([email protected]) 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, [email protected]

    Thank you very much, works perfect.

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