MU SQL Objective 7: Send Referral Electronic

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 7: Send Referral Electronic

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 msg_log as
( select referral_number, link_id, m.msg_id, text, date1 as msg_date, addr_id as msg_from, pat_id as patno, subject, recipient_op_id, delivery_status, ext_message_id from esmg_org_msg m
left outer join (

select delivery_id, msg_id, addr_id as recipient_op_id, delivery_status, ext_message_id  from esmg_delivery where is_external = 1 and ext_message_id is not null and ext_message_id <> '' ) d on d.msg_id = m.msg_id
inner join (select enc_id as referral_number, addr_id as link_id, attach_date, attach_id from enc_attach where enc_table = 'REFERALLETTER' and attach_table = 'Message' and attach_date between :Start_date and :end_date +1 ) a on a.attach_id = m.msg_id
where m.parent_id is null and m.is_external = 0 and m.pat_id is not null
and d.delivery_id is not null
)
select
case
when direct_email is not null and ext_message_id is not null and msg_id is not null and   recipient_op_id = recipient_id and manual_override = 1 then 'pass (sent electronically & manual override)'
when direct_email is not null and ext_message_id is not null and msg_id is not null and  recipient_op_id =  recipient_id then 'pass (sent electronically)'
when manual_override = 1 then 'pass for manual override'
when (direct_email is null or direct_email = '') then 'fail: no direct email for recipient'
when ( msg_id is null ) then 'fail: no direct message created'
when ext_message_id is null then 'fail: direct message not sent'
when recipient_op_id <> recipient_id then 'fail: sent successfully, but not to the referral recipient'
else 'undetermined'
end as det,
b.*  from (
select letterno as ref_referral_number, rf.patno as ppatno, date1 as ref_date, rf.p_addr_id, custom1, refmd, ab.addr_id as recipient_id, ab.direct_email, receipt_confirmed as manual_override,
msg_log.*
from referalletter rf
inner join register on register.patno = rf.patno
left outer join addressbook ab on ab.addr_id = rf.refmd_addr_id
left outer join msg_log on msg_log.referral_number = rf.letterno
where (rf.custom1 in ('REFERRAL')) and (rf.date1 between :start_date and :end_date ) and rf.P_addr_id = :provider
) b
order by ppatno


SQL Code: MySQL

To highlight and copy the code below to your clipboard, simply click the Copy button.

select
case
when direct_email is not null and ext_message_id is not null and msg_id is not null and   recipient_op_id = recipient_id and manual_override = 1 then 'pass (sent electronically & manual override)'
when direct_email is not null and ext_message_id is not null and msg_id is not null and  recipient_op_id =  recipient_id then 'pass (sent electronically)'
when manual_override = 1 then 'pass for manual override'
when (direct_email is null or direct_email = '') then 'fail: no direct email for recipient'
when ( msg_id is null ) then 'fail: no direct message created'
when ext_message_id is null then 'fail: direct message not sent'
when recipient_op_id <> recipient_id then 'fail: sent successfully, but not to the referral recipient'
else 'undetermined'
end as det,
b.*  from (
select letterno as ref_referral_number, rf.patno as ppatno, date1 as ref_date, rf.p_addr_id, custom1, refmd, ab.addr_id as recipient_id, ab.direct_email, receipt_confirmed as manual_override,
msg_log.*
from referalletter rf
inner join register on register.patno = rf.patno
left outer join addressbook ab on ab.addr_id = rf.refmd_addr_id
left outer join ( select referral_number, link_id, m.msg_id, text, date1 as msg_date, addr_id as msg_from, pat_id as patno, subject, recipient_op_id, delivery_status, ext_message_id from esmg_org_msg m
left outer join (

select delivery_id, msg_id, addr_id as recipient_op_id, delivery_status, ext_message_id  from esmg_delivery where is_external = 1 and ext_message_id is not null and ext_message_id <> '' ) d on d.msg_id = m.msg_id
inner join (select enc_id as referral_number, addr_id as link_id, attach_date, attach_id from enc_attach where enc_table = 'REFERALLETTER' and attach_table = 'Message' and attach_date between :Start_date and :end_date +1 ) a on a.attach_id = m.msg_id
where m.parent_id is null and m.is_external = 0 and m.pat_id is not null
and d.delivery_id is not null
) msg_log on msg_log.referral_number = rf.letterno
where (rf.custom1 in ('REFERRAL')) and (rf.date1 between :start_date and :end_date ) and rf.P_addr_id = :provider
) b
order by ppatno