Find the time taken between specific task flow for completing the coding task for an encounter

Transact-SQL
1

Hi All,

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)..

170109-image.png
170109-image.png1531×639 34.8 KB

Input table

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')

1 Like
2

don't know where you got the time change for the code incomplete records

;with cteStart as (
		Select event_time, Event_type, user_id, ctextID, vbillid
	      from #input
		  where Event_Type = 'Start Work Clicked'),
	 cteComplete as (
		Select event_time, Event_type, user_id, ctextID, vbillid
	      from #input
		  where Event_Type = 'Coding Complete Clicked'),
	cteUsers as (
		Select ctextID, vbillid, count(distinct user_id) as distinctUsers
	      from #input
		group by ctextID, vbillid
		  )
		   

select s.ctextID, s.vbillid, s.event_time, c.Event_Type, datediff(mi, s.event_Time, c.event_Time) as TimeDiffMinutes, u.DistinctUsers
  from cteStart S 
    join cteUsers u
		on s.ctextID = u.ctextID
		and s.vbillid = u.vbillid
	left join cteComplete c
		on s.ctextID = c.ctextID
		and s.vbillid = c.vbillid
3

@mike01 :Really appreciate your response!...What I am trying to do is to mine the task data for encounters and calculate the time difference in minutes for the time between tasks..

In the question above I have pasted the picture on how the time difference between tasks have to be calculated...The first task has to be Start Work clicked or Visitbill Button clicked and the second task or the following task has to be either Coding complete clicked or Home button clicked or Icon clicked and the time difference has to be calculated between these individual pair of tasks and the aggregate time in minutes have to be calculated..

Now the order of tasks (as provided in the picture above )have to be taken into account for calculating the time difference in minutes for an encounter.... If the first task is visitbill button clicked and the second task is also visitbillbutton clicked then the event_time of the first visitbill button clicked task needs to be ignored as it doesnt fall in the pair of the first and second pair of tasks as per the picture..

Basically the time difference between the pair of tasks as per the picture above should be aggregated in minutes..

4

Please provide a sample #output data

5

Hi Yosiasz,

The sample data is the one provided in the question...

Thanks,
Arun

6

time for coffee :laughing: