# question

## 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
('1/10/2021','2','1','3','127.39','108','362.78'),
('1/11/2021','1','1','2','48','62','55')

Thanks,
Arun

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

`````` ;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

output.png (4.7 KiB)
· 2

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

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)