| There are two versions of this SQL: Firebird and MySQL. The MySQL version only applies to clients who are have migrated to MySQL.. All other Practices should continue to use the Firebird version of this code. Click to expand and copy the code you need. If you are unsure which code to use, please check with your Practice Administrator. |
SQL for Objective 4: CPOE Diagnostic Orders
You will be prompted to enter the reporting period start and end date and the provider's OP ID number.
Report Usage Ideas
For more information about how this report is constructed, see Objective 4: Computerized Provider Order Entry (CPOE).
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
with r as (select dtr.* , diag_test_order_header_id from diag_test_result dtr
inner join diag_test_order dto on dto.trnsxno=dtr.orderlink
inner join diag_test_order_header doh on doh.diag_test_order_header_id = dto.diag_test_order_header_id where doh.date1 between :Start_date and :end_date)
select id, patno, date1, cast(case when position('fail',metric)>=1 and position('pass',metric)>=1 then 'pass: mixed' else metric end as char(2000)) as final_determination from
(
select id, patno, date1, list(distinct det,',') as metric from
(
select dtoh.diag_test_order_header_id as id, dtoh.patno, dtoh.date1, r.trnsxno, r.result_date, (result_date - dtoh.date1) as delta,
case when (result_date - dtoh.date1) >=0 then 'pass: result present' when trnsxno is null then 'pass: result pending' else 'fail' end as det
from diag_test_order_header dtoh
left outer join r on r.diag_test_order_header_id = dtoh.diag_test_order_header_id
where dtoh.date1 between :Start_date and :end_date and dtoh.reqby_addr_id = :provider_id and dtoh.macro_type_id <=1
) a group by id, patno, date1
)SQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select id, patno, date1, case when position('fail' in metric)>=1 and position('pass' in metric)>=1 then 'pass: mixed'
else metric end as final_determination from
(
select id, patno, date1, group_concat(distinct det separator ',') as metric from
(
select dtoh.diag_test_order_header_id as id, dtoh.patno, dtoh.date1, r.trnsxno, r.result_date, (result_date - dtoh.date1) as delta,
case when (result_date - dtoh.date1) >=0 then 'pass: result present' when trnsxno is null then 'pass: result pending' else 'fail' end as det
from diag_test_order_header dtoh
left outer join
(select dtr.* , doh.diag_test_order_header_id from diag_test_result dtr
inner join diag_test_order dto on dto.trnsxno=dtr.orderlink
inner join diag_test_order_header doh on doh.diag_test_order_header_id = dto.diag_test_order_header_id
where doh.date1 between :start_date and :end_date) r
on r.diag_test_order_header_id = dtoh.diag_test_order_header_id
where dtoh.date1 between :start_date and :end_date and dtoh.reqby_addr_id = :provider_id and dtoh.macro_type_id <=1
) a group by id, patno, date1
) t1
