MU SQL Objective 4: Diagnostic Orders CPOE

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.

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