Cacher is the code snippet organizer for pro developers

WITH TCLRxWritten_CTE
AS
(SELECT COUNT(DISTINCT prescriptions.prescriptionid) A
	from prescriptions
	WHERE CAST(prescriptions.rxdate as date) between @DateFrom AND @DateTo
	AND prescriptiontypeid IN (953)
	AND prescriptions.physicianid in (select physicianid from physician
where inhouse = 'Yes' and active_flag = 'Yes' and npi_number is not null)),
TCLOrdersCaptured_CTE
AS
(SELECT COUNT(DISTINCT o.orderid) B
	FROM orders o
	LEFT OUTER JOIN prescriptions P on o.prescriptionid = p.prescriptionid
	WHERE CAST(o.orderdate as date) between @DateFrom AND @DateTo
	AND prescriptiontypeid IN (953)
	AND (
		isNull(o.orderstatusid, 0) NOT IN (
			SELECT domainid
			FROM domain
			WHERE isQuote = 'Yes'
				AND domaintypeid = 86
			)
		)
	AND p.physicianid in (select physicianid from physician
where inhouse = 'Yes' and active_flag = 'Yes' and npi_number is not null))

select CAST(TCLOrdersCaptured_CTE.B as float)/CAST(TCLRxWritten_CTE.A as float)*100 
from TCLOrdersCaptured_CTE, TCLRxWritten_CTE