# Finding the number of visitbills coded and average time taken

821 Reputation points
2021-10-13T08:01:05.53+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,487 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,614 questions

1. 24,191 Reputation points
2021-10-13T08:59:47.577+00:00

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