Search code examples
sqlgoogle-bigquerydremel

finding time difference between rows

I need to calculate the claimed time of IssueID by user Total claim time is time from status claim till recent waiting. Looks a bit complex Kindly help out.

 IssueID    TransTime   User    Status
101 2019-08-23 0:25:41  Peter   CLAIMED
101 2019-08-23 0:25:44  Peter   CLAIMED
101 2019-08-23 0:26:12  Peter   WAITING
101 2019-08-23 20:14:13 Peter   CLAIMED
101 2019-08-23 20:14:16 Peter   CLAIMED
101 2019-08-23 20:14:52 Peter   WAITING
102 2019-08-24 8:59:19  Miller  CLAIMED
102 2019-08-24 8:59:56  Miller  CLAIMED
102 2019-08-24 9:00:09  Miller  WAITING
102 2019-08-24 9:00:17  Miller  CLAIMED
102 2019-08-24 9:00:20  Miller  CLAIMED
102 2019-08-25 21:56:52 Miller  WAITING`

For example, For peter total claim time start from '2019-08-23 0:25:41' till the first waiting time '2019-08-23 0:26:12' and next from '2019-08-23 20:14:13' till '2019-08-23 20:14:52'. All this time difference add up to the total time claimed by peter, which is around 31 seconds first and 39 seconds in the second time. Comes around 70 seconds.

Thanks in Advance

`

Solution

  • You can identify each group by counting the number of "waiting" after each row. Then use this information to get each claim period. So:

    select issueId,
       min(transTime) as min_time,
       max(transTime) as max_time),
       datetime_diff(min(transTime), max(transTime), second) as time_in_seconds
from (select t.*,
             countif(status = 'WAITING') over (partition by issueId order by transTime desc) as grp
      from t
      where status in ('WAITING', 'CLAIM')
     ) t
group by issueId, grp;

    I'm not sure if is exactly what you want -- you might want an additional level of aggregation. But this is the idea for calculating each period.