Finding the number of visitbills coded and average time taken

Question

Hi All,

Hope you are doing well!.. I am trying to do some analysis out of the BPO data...Combination of contextid and visitbillid represents an encounter... An encounter is assigned to the BPO on a specific assigned date and then they go through two paths - 1) the encounter gets just coded and then gets completed 2) the encounters gets coded and then gets QAED and then gets completed ..In the 1st case the coded date becomes the completed date ; In the 2nd case the QAED date becomes the completed date... I am trying to find the following stats from the above data:

1) By completed date - What are the number of encounters that just get coded ; what are the number of encounters that get coded and qaed ; What are the number of encounters that get completed ; What is the average time in hours (calculated as Assigneddate- codeddate) for just coded visitbills ; what is the averagetimein hours (calculated as assigneddate-qaedate) for codedandqaed visitbills ; What is the overall average time in hours (calculated as assigneddate -completeddate) ...Please find the DDL below for input and output ...can you please help me here..

Input table

create table ##input
(contextid int,
visitbillid int,
assigneddate datetime2,
codeddate datetime2,
qaeddate datetime2,
completed datetime2)

insert into ##input values
('561','21','1/3/2021 9:43:03 PM','1/10/2021 11:43:03 PM','','1/10/2021 11:43:03 PM'),
('561','178','1/5/2021 9:43:03 PM','1/6/2021 9:43:03 AM','1/10/2021 9:43:03 AM','1/10/2021 9:43:03 AM'),
('451','213','1/6/2021 9:43:03 PM','1/10/2021 10:30:03 AM','','1/10/2021 10:30:03 AM'),
('312','2','1/8/2021 9:43:03 PM','1/9/2021 11:43:03 AM','1/11/2021 11:43:03 AM','1/11/2021 11:43:03 AM'),
('312','12','1/9/2021 9:43:03 PM','1/11/2021 9:43:03 PM','','1/11/2021 9:43:03 PM')

Output table

create table ##output
(completeddate date,
noofvisitbillscoded int,
noofvisitbillscodedandqaed int,
totalvisitbillscompleted int,
averagetimeforcodedvisitbills float,
averagetimeforcodedandqaedvisitbills float,
overallaveragetime float)

insert into ##output values
('1/10/2021','2','1','3','127.39','108','362.78'),
('1/11/2021','1','1','2','48','62','55')

Thanks,
Arun

Answer 1

Hi @Hellothere8028 ,

Please refer to below and check whether it is helpful:

;with cte as (  
select cast(completed as date) completeddate ,iscode  
,iif(iscode=1,datediff(minute,assigneddate,codeddate),0) codeddatetime  
,iif(iscode=0,datediff(minute,assigneddate,qaeddate),0) qaeddatetime  
,datediff(minute,assigneddate,completed) overall  from   
(select *,iif(qaeddate='1900-01-01 00:00:00.0000000',1,0) iscode from ##input)a )  
select completeddate   
,sum(iif(iscode=1,1,0)) noofvisitbillscoded  
,sum(iif(iscode=1,0,1)) noofvisitbillscodedandqaed  
,count(1) totalvisitbillscompleted  
,cast(sum(codeddatetime)/sum(iif(iscode=1,1,0)) as float)/60 averagetimeforcodedvisitbills  
,cast(sum(qaeddatetime)/sum(iif(iscode=1,0,1)) as float)/60  averagetimeforcodedandqaedvisitbills  
,cast(sum(overall)/count(1) as float)/60  overallaveragetime  
from cte  
group by completeddate  

Output:

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.