Row based time difference based on condition for A

问题:

I have the following data in a table called TABLE:

EDIT: Added another couple rows with Characterid: 26052013030101, which was missed.

    /------------------------------------------------------------------------\
    |      CharacterID     |     EVENTTYPE     |        TRIGGERTIME          |
    |----------------------+-------------------+-----------------------------|
    |    11052016190101    |    START          |   2017-06-01 13:35:38.000   |
    |    11052016190101    |    END            |   2017-01-06 08:05:18.620   |
    |    01012016170101    |    START          |   2017-06-01 13:33:18.000   |
    |    01012016170101    |    Player Left    |   2017-06-01 13:35:21.000   |
    |    01012016170101    |    END            |   2017-06-01 13:38:22.000   |
    |    26052013030101    |    START          |   2017-06-01 13:35:39.000   |
    |    26052013030101    |    RESET          |   2017-06-01 13:35:50.000   |
    \------------------------------------------------------------------------/

I have written this query to to get the time difference based on START and END values of EVENTTYPE:

SELECT
    cp_start.characterid,
    MAX(cp_start.triggertime) AS start_time,
    cp_end.triggertime AS end_time,
    datediff(second, MAX(cp_start.triggertime), cp_end.triggertime)
FROM
    TABLE AS cp_start
INNER JOIN
    TABLE AS cp_end ON (
        cp_start.CharacterID= cp_end.CharacterID
    AND
        cp_end.triggertime > cp_start.triggertime)
WHERE cp_start.eventtype = 'START'
AND cp_end.eventtype = 'END'
GROUP BY cp_start.characterid, cp_Start.TriggerTime, cp_end.TriggerTime

However, what we want is to get the time difference for the above condition - i.e. START and END - and if there is any other event between START and END then we need to skip that particular CharacterID.

In the above example see CharacterID = 01012016170101, there is a row with EVENTTYPE='Player Left' row between the rows with START and END values for EVENTTYPE which need to be skipped or not to be considered.

EDIT: In the above, characterid = 26052013030101, has only START but no END. It has RESET, meaning we should not consider this value while displaying the result. END OF EDIT

How do we achieve this?

Secondly, Is there any easy method to achieve this in POWERBI and display the count and time difference?

回答1:

This will find every START record regardless of how many times a CharacterID starts a session and then finds the following END record:

declare @t table(CharacterID bigint,EVENTTYPE nvarchar(100),TRIGGERTIME datetime);
insert into @t values
 (11052016190101,'START','2017-01-01 13:35:38.000')
,(11052016190101,'END','2017-01-06 08:05:18.620')
,(01012013010101,'START','2017-06-01 13:33:18.000')
,(01012013010101,'Player Left','2017-06-01 13:35:21.000')
,(01012013010101,'END','2017-06-01 13:38:22.000')
,(01012013010101,'START','2017-07-01 13:33:18.000')
,(01012013010101,'Player Left','2017-07-01 13:35:21.000')
,(01012013010101,'END','2017-07-01 13:38:22.000');

with Starts as
(
    select CharacterID
            ,EVENTTYPE
            ,TRIGGERTIME
    from @t
    where EVENTTYPE = 'START'
)
select s.CharacterID
        ,s.TRIGGERTIME as StartTime
        ,e.TRIGGERTIME as EndTime
from Starts s
    outer apply (select top 1 TRIGGERTIME
                    from @t
                    where CharacterID = s.CharacterID
                        and TRIGGERTIME > s.TRIGGERTIME
                        and EVENTTYPE = 'END'
                    order by TRIGGERTIME
                ) e
order by CharacterID
        ,StartTime;

I have slightly changed your test data so that it actually makes sense, but using the data in the script above, the output is as follows:

CharacterID    | StartTime               | EndTime
---------------+-------------------------+------------------------
1012013010101  | 2017-06-01 13:33:18.000 | 2017-06-01 13:38:22.000
1012013010101  | 2017-07-01 13:33:18.000 | 2017-07-01 13:38:22.000
11052016190101 | 2017-01-01 13:35:38.000 | 2017-01-06 08:05:18.620

回答2:

Based on some assumptions:

• You only ever have one record per CharacterId for each of the two relevant EVENTTYPE values ("START" and "END")
• Any record with an EVENTTYPE value of "END" will always have a later datetime value in TRIGGERTIME than the record for the same CharacterId with an EVENTTYPE of "START".

You could use something like:

SELECT DISTINCT
    c.CharacterId,
    start.TRIGGERTIME AS StartTime,
    [end].TRIGGERTIME AS EndTime,
    DATEDIFF(s, start.TRIGGERTIME, [end].TRIGGERTIME) AS [TimeDiff(seconds)]
FROM [TABLE] c
OUTER APPLY
(
    SELECT TRIGGERTIME
    FROM [TABLE] s
    WHERE s.CharacterId = c.CharacterId
    AND s.EVENTTYPE = 'START'
) start
OUTER APPLY
(
    SELECT TRIGGERTIME
    FROM [TABLE] e
    WHERE e.CharacterId = c.CharacterId
    AND e.EVENTTYPE = 'END'
) [end]

If you want to present the time difference in a different way than just the number of seconds, that can be handled separately and there are lots of other questions on SO which deal with this.

You could equally move the OUTER APPLYs into subqueries in the SELECT clause, but this way make the logic a little easier to follow IMHO.