SQL for Objective 7: Response Returned 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 new_pts as (
select 'new patient' as reason, patno, pl.date1,   pl.cptcode  from procedure_list pl
inner join register on register.patno = pl.patno
where  (pl.cptcode like '9920%'  or pl.cptcode like '9938%')
and pl.date1 between :Start_date and :end_Date
and register.addr_id = :provider
), resp_track as
(
select
case when rf.custom1 = 'RESPONSE' then  'referral response' when rf.custom1 = 'TRACKING ENTRY' then 'tracking entry (not new pt' else 'no reason' end as reason,  
patno, date1,
case when rf.custom1 = 'RESPONSE' then  letterno when rf.custom1 = 'TRACKING ENTRY' then letterno else 'unknown' end as  ref_num,
 refmd as outside_entity,   rf.ELECTRONIC_REQUEST_RESPONSE , 'n/a' as cptcode,  rf.med_reconciliation_status from referalletter rf
inner join register on register.patno = rf.patno
where ( rf.custom1 = 'RESPONSE' OR (  rf.custom1 = 'TRACKING ENTRY' and   lower(reason) not like '%new patient%' ))
and (rf.date1 between :start_Date and :end_Date ) and rf.p_addr_id =:provider and status = 'COMPLETE'
order by patno),
tracking_new as
(
select   patno, ref_num, outside_entity,  pl.date1, ELECTRONIC_REQUEST_RESPONSE, med_reconciliation_status from procedure_list pl
inner join register on register.patno = pl.patno
inner join (select patno, ELECTRONIC_REQUEST_RESPONSE, letterno,  refmd as outside_entity, letterno as ref_num, med_reconciliation_status from referalletter where status='COMPLETE' and custom1 = 'TRACKING ENTRY' and date1 between :START_DATE AND :END_DATE and lower(reason) like '%new patient%') rl on rl.patno = pl.PATNO
where  (pl.cptcode like '9920%'  or pl.cptcode like '9938%')
and pl.date1 between :Start_date and :end_Date
and register.addr_id = :provider
)
select
case
when electronic_request_response = 1 then 'EXCLUSION'
when attach_table  is null then 'fail: no attachment'
when file_type <> 'CDA' then 'fail: attachment, but not a CDA'  
when file_type = 'CDA' and med_reconciliation_status not in (1,2) then 'pass: CDA attached/incorporated'
when file_type = 'CDA' and med_reconciliation_status  in (1,2) then 'pass with reconcilation'
 else 'undetermined' end as det,
a.*, cda.*  from
(
select new_pts.reason, new_pts.patno, new_pts.date1, new_pts.cptcode, tracking_new.ref_num, outside_entity, tracking_new.ELECTRONIC_REQUEST_RESPONSE, tracking_new.med_reconciliation_status from new_pts
left outer join tracking_new on new_pts.patno= tracking_new.patno
union
select reason, patno, date1, cptcode, ref_num, outside_entity, electronic_request_response, med_reconciliation_status from resp_track
) a
left outer join (
   select enc_id, attach_table, attach_id, file_type from enc_attach inner join ( select referenceid, file_type from docimage_metadata) docs on enc_attach.attach_id =docs.referenceid
  where enc_table = 'REFERALLETTER' and attach_date between :STart_date and :end_date +1)
cda on cda.enc_id = a.ref_num
order by reason, patno

SQL Code: MySQL

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

Copy

select
case
when electronic_request_response = 1 then 'EXCLUSION'
when attach_table  is null then 'fail@ no attachment'
when file_type <> 'CDA' then 'fail@ attachment, but not a CDA'  
when file_type = 'CDA' and med_reconciliation_status not in (1,2) then 'pass:@ CDA attached/incorporated'
when file_type = 'CDA' and med_reconciliation_status  in (1,2) then 'pass with reconcilation'
 else 'undetermined' end as det,
a.*, cda.*  from
(
select new_pts.reason, new_pts.patno, new_pts.date1, new_pts.cptcode, tracking_new.ref_num, outside_entity, tracking_new.ELECTRONIC_REQUEST_RESPONSE, tracking_new.med_reconciliation_status from (
select 'new patient' as reason, pl.patno, pl.date1,   pl.cptcode  from procedure_list pl
inner join register on register.patno = pl.patno
where  (pl.cptcode like '9920%'  or pl.cptcode like '9938%')
and pl.date1 between :start_date and :end_date
and register.addr_id = :provider
) new_pts
left outer join (
select   pl.patno, ref_num, outside_entity,  pl.date1, ELECTRONIC_REQUEST_RESPONSE, med_reconciliation_status from procedure_list pl
inner join register on register.patno = pl.patno
inner join (select patno, ELECTRONIC_REQUEST_RESPONSE, letterno,  refmd as outside_entity, letterno as ref_num, med_reconciliation_status from referalletter
 where status='COMPLETE' and custom1 = 'TRACKING ENTRY' and date1 between :start_date AND :end_date and lower(reason) like '%new patient%') rl on rl.patno = pl.PATNO
where  (pl.cptcode like '9920%'  or pl.cptcode like '9938%')
and pl.date1 between :start_date and :end_date
and register.addr_id = :provider
) tracking_new on new_pts.patno= tracking_new.patno
union
select reason, patno, date1, cptcode, ref_num, outside_entity, electronic_request_response, med_reconciliation_status from (
select
case when rf.custom1 = 'RESPONSE' then  'referral response' when rf.custom1 = 'TRACKING ENTRY' then 'tracking entry (not new pt' else 'no reason' end as reason,  
rf.patno, date1,
case when rf.custom1 = 'RESPONSE' then  letterno when rf.custom1 = 'TRACKING ENTRY' then letterno else 'unknown' end as  ref_num,
 refmd as outside_entity,   rf.ELECTRONIC_REQUEST_RESPONSE , 'n/a' as cptcode,  rf.med_reconciliation_status from referalletter rf
inner join register on register.patno = rf.patno
where ( rf.custom1 = 'RESPONSE' OR (  rf.custom1 = 'TRACKING ENTRY' and   lower(reason) not like '%new patient%' ))
and (rf.date1 between :start_date and :end_date ) and rf.p_addr_id =:provider and status = 'COMPLETE'
order by patno) resp_track
) a
left outer join (
   select enc_id, attach_table, attach_id, file_type from enc_attach inner join ( select referenceid, file_type from docimage_metadata) docs on enc_attach.attach_id =docs.referenceid
  where enc_table = 'REFERALLETTER' and attach_date between :start_date and date_add(cast(:end_date as date),interval 1 day))
cda on cda.enc_id = a.ref_num
order by reason, patno