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.

Report Usage Ideas

For more information about how this report is constructed, see Objective 7: Health Information Exchange.

SQL Code: Firebird

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

Copy

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.

Copy

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