0

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

`

syncdm2012
  • 445
  • 2
  • 10
  • 22

2 Answers2

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Thanks a lot Gordon Linoff. I just added one more level of agg. & it works fine. group by issueId, user,grp; And a top query to sum the time by issueid and user – syncdm2012 Sep 23 '19 at 11:24
  • Gordon Linoff, can you please suggest if some rows for peter comes after miller. How do I handle. – syncdm2012 Sep 24 '19 at 10:55
  • @syncdm2012 . . . Ask a new question with sample data and desired results to illustrate the issue that you are having. – Gordon Linoff Sep 24 '19 at 12:28
0

Below is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 101 IssueID, TIMESTAMP '2019-08-23 0:25:41' TransTime, 'Peter' User, 'CLAIMED' Status UNION ALL
  SELECT 101, '2019-08-23 0:25:44', 'Peter', 'CLAIMED' UNION ALL
  SELECT 101, '2019-08-23 0:26:12', 'Peter', 'WAITING' UNION ALL
  SELECT 101, '2019-08-23 20:14:13', 'Peter', 'CLAIMED' UNION ALL
  SELECT 101, '2019-08-23 20:14:16', 'Peter', 'CLAIMED' UNION ALL
  SELECT 101, '2019-08-23 20:14:52', 'Peter', 'WAITING' UNION ALL
  SELECT 102, '2019-08-24 8:59:19', 'Miller', 'CLAIMED' UNION ALL
  SELECT 102, '2019-08-24 8:59:56', 'Miller', 'CLAIMED' UNION ALL
  SELECT 102, '2019-08-24 9:00:09', 'Miller', 'WAITING' UNION ALL
  SELECT 102, '2019-08-24 9:00:17', 'Miller', 'CLAIMED' UNION ALL
  SELECT 102, '2019-08-24 9:00:20', 'Miller', 'CLAIMED' UNION ALL
  SELECT 102, '2019-08-25 21:56:52', 'Miller', 'WAITING' 
)
SELECT IssueID, SUM(waiting_time) total_waiting_time 
FROM (
  SELECT IssueID, TIMESTAMP_DIFF(MAX(TransTime), MIN(TransTime), SECOND) waiting_time
  FROM (
    SELECT *, COUNTIF(start) OVER(PARTITION BY IssueID ORDER BY TransTime) waiting
    FROM (
      SELECT *, ('CLAIMED' = status AND IFNULL(LAG(status) OVER(PARTITION BY IssueID ORDER BY TransTime), 'WAITING') = 'WAITING') start
      FROM `project.dataset.table`
      WHERE status IN ('CLAIMED', 'WAITING')
    )
  )
  GROUP BY IssueID, waiting
)
GROUP BY IssueID
ORDER BY IssueID

with result 

Row IssueID total_waiting_time   
1   101     70   
2   102     133045
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230