Finding the number of visitbills coded and average time taken

Transact-SQL
1

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

2

try this, but not sure if the match on date includes hh:mi:ss or not

select completed, NumEncountersCoded, NumEncountersQAed, NumEncountersCompleted,
		case when AverageTimeInHoursCodedCount = 0 
			 then 0
		else AverageTimeInHoursCoded/AverageTimeInHoursCodedCount
		end as AverageTimeInHoursCoded,
		case when AverageTimeInHoursQACount = 0 
			 then 0
		else AverageTimeInHoursQA/AverageTimeInHoursQACount
		end as AverageTimeInHoursQA,
		AverageTotalTimeHours
  from(
Select completed,
	   sum(case when codeddate = completed then 1 else 0 end) NumEncountersCoded,
	   sum(case when qaeddate = completed then 1 else 0 end) NumEncountersQAed,
	   sum(case when codeddate = completed or qaeddate = completed then 1 else 0 end) NumEncountersCompleted,
	   sum(case when codeddate = completed then DateDiff(hour,assignedDate,codeddate) else 0 end) AverageTimeInHoursCoded,
	   sum(case when codeddate = completed then 1 else 0 end) as AverageTimeInHoursCodedCount,
	   sum(case when qaeddate = completed then DateDiff(hour,assignedDate,qaeddate) else 0 end) AverageTimeInHoursQA,
	   sum(case when qaeddate = completed then 1 else 0 end) as AverageTimeInHoursQACount,
	   avg(DateDiff(hour,assignedDate,completed) ) AverageTotalTimeHours
from #input
group by completed) v
3

Thank you so much @mike01