Finding the distinct number of coders and number of encounters completed per day

Transact-SQL
1

Hi All,

Hope you are doing well!... A combination of contextid and visitbillid represents an encounter ...An encounter can be 1) Just coded by one person and then completed 2) coded by one person and then qaed by another person and then completed...

  1. I am trying to identify the distinct count of coders (both coders and qa coders) who have coded on a particular day and the number of encounters completed on a day denoted by the completed field

  2. I am trying to identify the distinct count of coders who have coded in a particular completed month and the number of encounters completed in that month...

Please find the DDL below for the input table and the output table 1 and output table 2...can you please help me here..

Input

create table ##input
(contextid int,
visitbillid int,
completed datetime2,
codedby varchar(20),
qaby varchar(20)
)

insert into ##input values
('561','21','1/10/2021 11:43:03 PM','ca3','ca5'),
('561','178','1/10/2021 9:43:03 AM','ca4',''),
('451','213','1/10/2021 10:30:03 AM','ca5','ca6'),
('312','2','1/11/2021 11:43:03 AM','ca3','ca2'),
('312','12','1/11/2021 9:43:03 PM','ca6',''),
('211','134','1/11/2021 5:30:03 PM','ca5','ca1'),
('214','231','1/12/2021 3:30:03 PM','ca4','ca2'),
('234','111','1/12/2021 3:30:03 PM','ca5',''),
('345','121','1/12/2021 3:30:03 PM','ca2','ca3'),
('443','789','2/1/2021 3:30:03 PM','ca4','ca1'),
('211','991','2/1/2021 3:59:03 PM','ca1',''),
('331','2131','2/2/2021 3:59:03 PM','ca5','ca2'),
('1071','321','2/2/2021 6:59:03 PM','ca2','ca6'),
('777','121','2/10/2021 6:59:03 PM','ca4','ca1'),
('1231','1211','2/10/2021 6:59:03 PM','ca1','ca6')

output1

create table ##output1
(completed date,
distinctnumberofcoders int,
numberofcompletedvisitbills int
)

insert into ##output1 values
('1/10/2021','4','3'),
('1/11/2021','5','3'),
('1/12/2021','4','3'),
('2/1/2021','2','2'),
('2/2/2021','3','2'),
('2/10/2021','3','2')

output2

create table ##output2
(completed date,
distinctnumberofcoders int,
numberofcompletedvisitbills int
)

insert into ##output2 values
('1/1/2021','6','9'),
('2/1/2021','5','6')

Thanks,
Arun

2 
WITH UnPivoted
AS
(
	SELECT DATEADD(day, DATEDIFF(day, 0, I.completed ), 0) AS completed
		--DATEADD(year, DATEDIFF(year, 0, I.completed ), 0) AS completed
		,C.coder
		,T.CodeType
	FROM #input I
		CROSS APPLY
		(
			VALUES('coded'), ('qa')
		) T (CodeType)
		CROSS APPLY
		(
			VALUES
			(
				CASE
					WHEN T.CodeType = 'coded'
					THEN I.codedby
					ELSE I.qaby
				END
			)
		) C (coder)
	WHERE LEN(C.coder) > 0
)
SELECT completed
	,COUNT(DISTINCT coder) AS distinctnumberofcoders
	,SUM(CASE WHEN CodeType = 'coded' THEN 1 ELSE 0 END) AS numberofcompletedvisitbills
FROM UnPivoted
GROUP BY completed;