| 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 lists all billed diagnoses of patients seen between two dates you specify, along with the rendering provider’s name.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- The diagnosis could be any billed diagnosis -- sick or well visit, blood draw/nurse visit only, etc.
- Each diagnosis will appear once for each patient/provider combination. For example, if patient Johnny had a billed dx of ADHD seen once by Dr. A and twice by Dr. B, Johnny appears twice on the list (once with Dr A and once with Dr B), not three times.
- If you have a large practice, pick a conservative date range to start.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select distinct a.patno, dx, fname, lname, birthdat, staffname, cast(list(date1) as char(2000)) as dos from
(
select patno, rend_addr_id, dxcode1 as dx, date1 from archive_transactions where date1 between :Start_date and :end_date and cptcode not in ('1','2','3','4') and dxcode1 is not null and archive_flag = 1
union
select patno, rend_addr_id, dxcode2 as dx, date1 from archive_transactions where date1 between :Start_date and :end_date and cptcode not in ('1','2','3','4') and dxcode2 is not null and archive_flag = 1
union
select patno, rend_addr_id, dxcode3 as dx, date1 from archive_transactions where date1 between :Start_date and :end_date and cptcode not in ('1','2','3','4') and dxcode3 is not null and archive_flag = 1
union
select patno, rend_addr_id, dxcode4 as dx, date1 from archive_transactions where date1 between :Start_date and :end_date and cptcode not in ('1','2','3','4') and dxcode4 is not null and archive_flag = 1
) a
inner join register on register.patno = a.patno
inner join staff1 on staff1.staffid = rend_addr_id
where dx is not null and dx <> ''
group by a.patno, dx, fname, lname, birthdat, staffnameSQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select
distinct a.patno,
dx,
fname,
lname,
birthdat,
staffname,
group_concat(cast(date1 as date) separator ',') as dos
from
(
select
patno,
rend_addr_id,
dxcode1 as dx,
date1
from
archive_transactions
where
date1 between :start_date
and :end_date
and cptcode not in ('1', '2', '3', '4')
and dxcode1 is not null
and archive_flag = 1
union
select
patno,
rend_addr_id,
dxcode2 as dx,
date1
from
archive_transactions
where
date1 between :start_date
and :end_date
and cptcode not in ('1', '2', '3', '4')
and dxcode2 is not null
and archive_flag = 1
union
select
patno,
rend_addr_id,
dxcode3 as dx,
date1
from
archive_transactions
where
date1 between :start_date
and :end_date
and cptcode not in ('1', '2', '3', '4')
and dxcode3 is not null
and archive_flag = 1
union
select
patno,
rend_addr_id,
dxcode4 as dx,
date1
from
archive_transactions
where
date1 between :start_date
and :end_date
and cptcode not in ('1', '2', '3', '4')
and dxcode4 is not null
and archive_flag = 1
) a
inner join register on register.patno = a.patno
inner join staff1 on staff1.staffid = rend_addr_id
where
dx is not null
and dx <> ''
group by
a.patno,
dx,
fname,
lname,
birthdat,
staffname
