CODEWITHSUNDEEP
mysqltableau-apidatediffcalculated-field
Brian Stump
Brian Stump

Reputation: 1

Calculate Time Difference in Single Column Based on Criteria from Other Columns in Tableau

I have a database table that acts as a status change log. All timestamps are provided in a single column. I need to calculate the elapsed time (in minutes) between specific status changes for each "on_id". As you can see from my sample data, I can't simply calculate the difference from one row to the next since there are unrelated rows that come between status changes for a single "on_id."

id          state                 from_state                to_state                   start_time             on_id   user_id 
45   transition_failed      volunteer_to_donor       claimed                  2016-11-28 18:05:59.509807+00      10        53  
 1   transition_completed   drafting                 available                2016-11-10 19:56:16.454458+00       2        12  
 2   transition_failed      available                available                2016-11-10 19:57:01.199609+00       2        12  
 3   transition_failed      available                available                2016-11-10 19:58:08.134549+00       2        12  
28   transition_completed   volunteer_to_donor       volunteer_to_recipient   2016-11-22 22:14:57.060536+00       9        51  
 4   transition_completed   drafting                 available                2016-11-14 16:36:17.802104+00       3        12  
 5   transition_completed   drafting                 available                2016-11-16 14:59:56.925226+00       5        15  
29   transition_failed      volunteer_to_recipient   volunteer_to_donor       2016-11-22 22:51:01.250038+00       9        51  
 6   transition_completed   drafting                 available                2016-11-16 18:04:04.172773+00       6        15  
 7   transition_completed   available                claimed                  2016-11-16 18:05:23.30427+00        6        16  
30   transition_failed      volunteer_to_recipient   volunteer_to_recipient   2016-11-22 22:51:12.458881+00       9        51  
 8   transition_completed   claimed                  volunteer_to_donor       2016-11-16 18:05:28.546312+00       6        16  
 9   transition_completed   volunteer_to_donor       volunteer_to_recipient   2016-11-16 18:05:39.388517+00       6        16  
10   transition_failed      volunteer_to_recipient   volunteer_to_recipient   2016-11-16 18:05:48.772071+00       6        16  
11   transition_completed   volunteer_to_recipient   complete                 2016-11-16 18:06:56.9068+00         6        16  
31   transition_completed   volunteer_to_recipient   complete                 2016-11-22 22:51:38.570253+00       9        51  
12   transition_completed   drafting                 available                2016-11-21 15:11:01.842671+00       7        12

I think that I need a calculated field, but I can't figure out the syntax or functions to use.

For example, I'd like to be able to calculate the elapsed time for each "on_id" to go from "available" status to "claimed" status. For on_id 6, this took 1min and 19sec.

id          state                 from_state                to_state                   start_time             on_id   user_id 

6   transition_completed   drafting                 available                2016-11-16 18:04:04.172773+00       6        15  
7   transition_completed   available                claimed                  2016-11-16 18:05:23.30427+00        6        16

Upvotes: 0

Views: 252

Answers (1)

jose_bacoy
jose_bacoy

Reputation: 12704

I created sample table and sql here: hope it helps.

Create table tbl(
  Id int, 
  to_state varchar (10), 
  On_id int,
dte date);

 Insert into tbl values ( 1, 'available', 6, '2018-02-22')
 , ( 2, 'claimed' , 6 , '2018-02-25')
 , ( 3, 'drafted' , 6 , '2018-02-23);

Sql:
Select distinct 
t1.on_id,
t2.availstartdte,
t2.claimstartdte,
datediff(t2.claimstartdte, t2.availstartdte) as date_diff
From tbl t1
Join ( select id, 
      (Select Max(b.dte) from tbl b where b.to_state='available' and a.on_id=b.on_id) as availstartdte,
      (Select Max(b.dte) from tbl b where b.to_state='claimed' and a.on_id=b.on_id) as claimstartdte
      From tbl a
      Where a.to_state in('available', 'claimed')
      ) t2
 On t1.id = t2.id

Result:

on_id   availstartdte   claimstartdte   date_diff
6             2018-02-22    2018-02-25          3

Upvotes: 0

Related Questions