| 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. |
About
This report shows all CPTs and charges, payments and adjustments, claim notes and adjustment codes, diagnoses, and age of patient at the time of service for a date of service range that you specify.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- The column MONTHS_OLD gives the patient’s age in months on the date of service.
- INVOICENO is the claim number associated with the charge.
- PENDING is the balance on the claim (either patient-side or insurance-side) once there has been at least one payment or adjustment on the line item. A PENDING of 0 means the claim has been completely paid/adjusted and is closed.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select at1.patno, birthdat, (datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,date1))) as months_old, at1.invoiceno, at1.date1, at1.cptcode, at1.mod1, at1.mod2, at1.ins_carrier_code, at1.ins_carrier_code_other, at1.charge, at1.units, at1.trnsxno, at1.pos, at1.dxcode1, at1.dxcode2, at1.dxcode3, at1.dxcode4, at2.p as ins_paid, at2.c as pt_paid, at2.p + at2.c as total_paid,at2.ins_adj, at2.pt_adj, round(at1.charge-at2.p-at2.c-at2.ins_adj-at2.pt_adj,2) as pending,
claim_note , cast(ins_adj_codes as char(10000)) as ins_adj_codes
from
(select date1, patno, invoiceno, cptcode,mod1, mod2, ins_carrier_code, ins_carrier_code_other, charge, trnsxno, pos, units, dxcode1, dxcode2, dxcode3, dxcode4 from archive_transactions where archive_flag = 1 and date1 between :Start_date and :enddate and cptcode not in ('1','2','3','4','PLA')) at1
inner join register on register.patno = at1.patno
left outer join
(select round(sum(payment),2) as p, round(sum(copayrecd),2) as c, sum(adjustment) as ins_adj, sum(copayadjustment) as pt_adj, txnopaid, list(descript,',') as ins_adj_codes from
(select copayrecd, payment, adjustment, copayadjustment, txnopaid, c.descript from archive_transactions
left outer join (select code_id, descript from code_table where group_id = 1) c on c.code_id = archive_transactions.adj_code
where archive_flag =1 )
group by txnopaid) at2
on at2.txnopaid = at1.trnsxno
left outer join claims1 on claims1.invoiceno = at1.invoiceno
order by patno, invoicenoSQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select
at1.patno,
birthdat,
floor(timestampdiff(month, birthdat, date1)) as months_old,
at1.invoiceno,
at1.date1,
at1.cptcode,
at1.mod1,
at1.mod2,
at1.ins_carrier_code,
at1.ins_carrier_code_other,
at1.charge,
at1.units,
at1.trnsxno,
at1.pos,
at1.dxcode1,
at1.dxcode2,
at1.dxcode3,
at1.dxcode4,
at2.p as ins_paid,
at2.c as pt_paid,
at2.p + at2.c as total_paid,
at2.ins_adj,
at2.pt_adj,
round(
at1.charge - at2.p - at2.c - at2.ins_adj - at2.pt_adj,
2
) as pending,
claim_note,
ins_adj_codes
from
(
select
date1,
patno,
invoiceno,
cptcode,
mod1,
mod2,
ins_carrier_code,
ins_carrier_code_other,
charge,
trnsxno,
pos,
units,
dxcode1,
dxcode2,
dxcode3,
dxcode4
from
archive_transactions
where
archive_flag = 1
and date1 between :start_date
and :end_date
and cptcode not in ('1', '2', '3', '4', 'PLA')
) at1
inner join register on register.patno = at1.patno
left outer join (
select
round(sum(payment), 2) as p,
round(sum(copayrecd), 2) as c,
sum(adjustment) as ins_adj,
sum(copayadjustment) as pt_adj,
txnopaid,
group_concat(descript separator ',') as ins_adj_codes
from
(
select
copayrecd,
payment,
adjustment,
copayadjustment,
txnopaid,
c.descript
from
archive_transactions
left outer join (
select
code_id,
descript
from
code_table
where
group_id = 1
) c on c.code_id = archive_transactions.adj_code
where
archive_flag = 1
) t1
group by
txnopaid
) at2 on at2.txnopaid = at1.trnsxno
left outer join claims1 on claims1.invoiceno = at1.invoiceno
order by
patno,
invoiceno
