SQL for Objective 7: Response to Referral Reconciled

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 patient)' else 'unknown reason' end as reason,
 patno, date1, 'n/a' as cptcode,  letterno as response_number,   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,  pl.date1,  letterno as response_number, med_reconciliation_status from procedure_list pl
inner join register on register.patno = pl.patno
inner join (select patno, med_reconciliation_status, letterno 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 med_reconciliation_status in (1,2) then 'pass' else 'fail' end as metric,
 a.* from
(
select new_pts.*, tracking_new.response_number, tracking_new.med_reconciliation_status from new_pts
left outer join tracking_new on new_pts.patno= tracking_new.patno
union
select * from resp_track
) a

SQL Code: MySQL

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

Copy

select case when med_reconciliation_status in (1,2) then 'pass' else 'fail' end as metric,
 a.* from
(
select new_pts.*, tracking_new.response_number, tracking_new.med_reconciliation_status from (
select 'new patient' as reason, register.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   register.patno,  pl.date1,  letterno as response_number, med_reconciliation_status from procedure_list pl
inner join register on register.patno = pl.patno
inner join (select patno, med_reconciliation_status, letterno 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 * from ( select case when rf.custom1 = 'RESPONSE' then  'referral response' when rf.custom1 = 'TRACKING ENTRY' then 'tracking entry (not new patient)' else 'unknown reason' end as reason,
 register.patno, date1, 'n/a' as cptcode,  letterno as response_number,   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