ArunChandramouli-6978 avatar image
0 Votes"
ArunChandramouli-6978 asked MelissaMa-msft commented

Finding the number of visitbills coded and average time taken

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MelissaMa-msft avatar image
2 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @ArunChandramouli-6978,

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


Best regards,

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.

output.png (4.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MelissaMa-msft : Really appreciate your response!...Can you please let me know what I should do if I get a division by zero error...

0 Votes 0 ·

Hi @ArunChandramouli-6978,

You could use below methods:

For example, col2=0.

Method 1: SQL NULLIF Function

 SELECT col1 / NULLIF(col2,0)

Method 2: Using CASE statement to avoid divide by zero error

 select case when col2=0 then null else col1/col2 end

Method 3: SET ARITHABORT OFF(not recommended)

If above are not helpful, please provide your data. Thanks.

Best regards,

0 Votes 0 ·