title: Snowflake Sql Query to find the time taken between specific task flow for completing the coding task for an encounter
body: <p><a href="https://i.stack.imgur.com/nnNUa.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nnNUa.jpg" alt="enter image description here" /></a>Hope you are doing well!.. An encounter is a combination of ctextid and vbillid.. There are multiple events that place before an encounter is coding completed.. Sometimes the tasks don't get coding completed at all... Following represents the tasks flow (eventtype) for which the difference in the event time should be calculated in minutes...Please find below the input and the output tables...can you please help me here.. Distinctnumberofcoders is calculated as distinct count of user_id)..</p>
  <p>170109-image.png</p>
  <p>Input table</p>
  <pre><code>create table ##input
  (event_time datetime2,
  event_type varchar(1000),
  user_id varchar(100),
  ctextid int,
  vbillid int)
  
  insert into ##input values
  ('1/11/2022 1:21:02 PM','Start Work Clicked','am21','10021','21'),
  ('1/11/2022 1:46:02 PM','Coding Complete Clicked','am21','10021','21'),
  ('1/12/2022 1:46:02 PM','Start Work Clicked','am31','78321','561'),
  ('1/12/2022 1:51:02 PM','Home Button Clicked','am31','78321','561'),
  ('1/12/2022 2:04:02 PM','VisitBill Button Clicked','am12','78321','561'),
  ('1/12/2022 2:24:02 PM','Coding Complete Clicked','am12','78321','561'),
  ('1/12/2022 1:55:02 PM','Start Work Clicked','am78','5561','78'),
  ('1/12/2022 2:10:02 PM',' Icon Clicked','am78','5561','78'),
  ('1/12/2022 2:15:02 PM','VisitBill Button Clicked','am89','5561','78'),
  ('1/12/2022 2:44:02 PM','Coding Complete Clicked','am89','5561','78'),
  ('1/22/2022 9:55:02 AM','Start Work Clicked','am115','789','89'),
  ('1/22/2022 10:25:02 AM','Home Button Clicked','am115','789','89'),
  ('1/22/2022 10:45:02 AM','VisitBill Button Clicked','am115','789','89'),
  ('1/22/2022 11:10:02 AM',' Icon Clicked','am115','789','89'),
  ('1/22/2022 11:25:02 AM','VisitBill Button Clicked','am115','789','89'),
  ('1/22/2022 11:40:02 AM','Coding Complete Clicked','am115','789','89'),
  ('1/23/2022 9:55:02 AM','Start Work Clicked','am115','9918','890'),
  ('1/23/2022 10:25:02 AM','Home Button Clicked','am115','9918','890'),
  ('1/23/2022 10:45:02 AM','VisitBill Button Clicked','am115','9918','890'),
  ('1/23/2022 11:10:02 AM',' Icon Clicked','am115','9918','890'),
  ('1/23/2022 11:25:02 AM','VisitBill Button Clicked','am115','9918','890'),
  ('1/23/2022 11:30:02 AM','VisitBill Button Clicked','am115','9918','890'),
  ('1/23/2022 11:40:02 AM','Coding Complete Clicked','am115','9918','890'),
  ('1/25/2022 10:55:02 AM','Start Work Clicked','am115','10021','981'),
  ('1/25/2022 11:15:02 AM','Home Button Clicked','am115','10021','981'),
  ('1/25/2022 11:23:02 AM','VisitBill Button Clicked','am115','10021','981'),
  ('1/25/2022 9:45:02 AM','Start Work Clicked','am118','9021','904'),
  ('1/25/2022 10:05:02 AM','Home Button Clicked','am118','9021','904'),
  ('1/25/2022 10:23:02 AM','VisitBill Button Clicked','am120','9021','904'),
  ('1/25/2022 10:43:02 AM','Icon Clicked','am120','9021','904')
  
  
  Output table
  
  create table ##output
  (ctextid int,
  vbillid int,
  eventdate date,
  comment varchar(1000),
  timetakeninminutes float,
  distinctnumberofcoders int)
  
  insert into ##output values
  
  ('10021','21','44572.5562731481','coding completed','25','1'),
  ('78321','561','44573.5771064815','coding completed','25','2'),
  ('5561','78','44573.5937731481','coding completed','44','2'),
  ('789','89','44583.4479398148','coding completed','70','1'),
  ('9918','890','44584.4479398148','coding completed','65','1'),
  ('10021','981','44586.4687731481','coding incomplete','20','1'),
  ('9021','904','44586.4465509259','coding incomplete','40','2')
  </code></pre>