About

A sample image of this SQL report run in the Database Viewer is shown below:

Caveats

• Voided amounts are excluded.
• “Payments received dates” are defined as the OP payment date, rather than the posted payment date or the daysheet date.

SQL Code: Firebird

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

Copy

select round(sum(total_paid),2) as total_payment, round(sum(total_adjusted),2) as total_adjustment from
(
select patno, dos, cptcode, total_paid, total_adjusted from
(
select patno, date1 as dos, trnsxno, cptcode from archive_transactions at1 where 
archive_Flag = 1 and date1 between :dos_start and :dos_end and txnopaid = 0
) a
left outer join
(select txnopaid, sum(payment+copayrecd) as total_paid, sum(copayadjustment+adjustment) as total_adjusted from archive_transactions where archive_flag = 1 and date1 between :payment_start and :payment_end and txnopaid >0 group by txnopaid) b on a.trnsxno = b.txnopaid


where ( total_paid >= 0.01 or total_adjusted >= 0.01)
)

SQL Code: MySQL

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

Copy

select a.patno, dos, cptcode, staffname as rendering_provider,code1 as appt_type, total_ins_paid, total_ins_adjusted from
(
select patno, date1 as dos, trnsxno, rend_addr_id, cptcode from archive_transactions at1 where 
archive_Flag = 1 and date1 between :dos_start_date and :dos_end_date and txnopaid = 0
) a
left outer join
(select txnopaid, sum(payment) as total_ins_paid, sum(adjustment) as total_ins_adjusted from archive_transactions where archive_flag = 1 and date1 between :payment_start_date and :payment_end_date and 
txnopaid >0 group by txnopaid) b on a.trnsxno = b.txnopaid
left outer join staff1 on 
staff1.staffid = a.rend_addr_id
left outer join (select appt_date,code1, patno from schedule where appt_date between :dos_start_date and :dos_end_date and visit_status not in ( 'Cancelled','No Show')) s on a.patno = s.patno and s.appt_date = a.dos
where ( total_ins_paid >= 0.01 or total_ins_adjusted >= 0.01)