Cacher is the code snippet organizer for pro developers

-- get same day capture rate - Order filled on same day Rx was written
WITH RxWritten_CTE
AS
(SELECT COUNT(DISTINCT prescriptions.prescriptionid) A --count prescriptions
	from prescriptions
	WHERE CAST(prescriptions.rxdate as date) between @DateFrom AND @DateTo --certain date range
	AND prescriptiontypeid IN (953) --spectacle lens type from domain table
	-- Find inhouse doctor
	AND prescriptions.physicianid in (select physicianid from physician
where inhouse = 'Yes' and active_flag = 'Yes' and npi_number is not null)),
OrdersCaptured_CTE
AS
(SELECT COUNT(DISTINCT o.orderid) B --counts unique order id numbers
	FROM orders o
	LEFT OUTER JOIN prescriptions P on o.prescriptionid = p.prescriptionid
	WHERE CAST(o.orderdate as date) = CAST(p.rxdate as date) --order date and rx date equal
	AND CAST(p.rxdate as date) between @DateFrom AND @DateTo --rxdate in date range, likely unnecessary
	AND CAST(o.orderdate as date) between @DateFrom AND @DateTo --orderdate in date range
	AND prescriptiontypeid IN (953) --spectacle lenses
	--cannot be quote or status 86
	AND (
		isNull(o.orderstatusid, 0) NOT IN (
			SELECT domainid
			FROM domain
			WHERE isQuote = 'Yes'
				AND domaintypeid = 86
			)
		)
	--limit to inhouse docs
	AND p.physicianid in (select physicianid from physician
where inhouse = 'Yes' and active_flag = 'Yes' and npi_number is not null))

select CAST(OrdersCaptured_CTE.B as float)/CAST(RxWritten_CTE.A as float)*100 from OrdersCaptured_CTE, RxWritten_CTE