About

Shows the number of each type of well visit, the sum of new and established well visits, and grand total of well visits for a period you specify, grouped by location and rendering provider.

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

Caveats

• Takes a while to run. Do not run for periods exceeding a year at a time.
• Well visits are defined as 9938x and 9939x billed through OP.

SQL Code: Firebird

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

Copy

With raw_sums as (
select location.id as locnum, p.rend_addr_id, s99381, s99382, s99383, s99384, s99385, s99391, s99392, s99393, s99394, s99395 from location
left outer join
 (select distinct loc_id, rend_addr_id from archive_transactions where date1 between :Start_date and :end_Date and cptcode between '99381' and '99395') p on p.loc_id = location.id
left outer join
 (select loc_id, rend_addr_id,count(cptcode) as s99381 from archive_transactions where cptcode = '99381' and archive_Flag = 1 and date1 between :Start_date and :end_Date group by loc_id, rend_addr_id ) a on a.loc_id = location.id and a.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99382 from archive_transactions where cptcode = '99382' and archive_Flag = 1 and date1 between :Start_date and :end_Date  group by loc_id, rend_addr_id) b on b.loc_id = location.id and   b.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99383 from archive_transactions where cptcode = '99383' and archive_Flag = 1 and date1 between :Start_date and :end_Date  group by loc_id, rend_addr_id) c on c.loc_id = location.id and   c.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99384 from archive_transactions where cptcode = '99384' and archive_Flag = 1 and date1 between :Start_date and :end_Date  group by loc_id, rend_addr_id) d on d.loc_id = location.id and   d.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99385 from archive_transactions where cptcode = '99385' and archive_Flag = 1 and date1 between :Start_date and :end_Date  group by loc_id, rend_addr_id) e on e.loc_id = location.id and   e.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99391 from archive_transactions where cptcode = '99391' and archive_Flag = 1 and date1 between :Start_date and :end_Date  group by loc_id, rend_addr_id) f on f.loc_id = location.id and   f.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99392 from archive_transactions where cptcode = '99392' and archive_Flag = 1 and date1 between :Start_date and :end_Date  group by loc_id, rend_addr_id) g on g.loc_id = location.id and   g.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99393 from archive_transactions where cptcode = '99393' and archive_Flag = 1 and date1 between :Start_date and :end_Date  group by loc_id, rend_addr_id) h on h.loc_id = location.id and   h.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99394 from archive_transactions where cptcode = '99394' and archive_Flag = 1 and date1 between :Start_date and :end_Date  group by loc_id, rend_addr_id) i on i.loc_id = location.id and   i.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99395 from archive_transactions where cptcode = '99395' and archive_Flag = 1 and date1 between :Start_date and :end_Date  group by loc_id, rend_addr_id) j on j.loc_id = location.id and   j.rend_addr_id = p.rend_addr_id

where location.archive_flag <> 1

), all_locs as
(select id, loc_name from location where archive_flag <> 1),

provider_new_sums as
(
Select loc_id, rend_addr_id, count(cptcode) as total_new from
(
Select loc_id, rend_addr_id, cptcode from archive_Transactions at1
where date1 between :Start_date and :end_Date and  (cptcode between '99381' and '99385') and at1.archive_Flag = 1
) group by loc_id,  rend_addr_id
),
provider_est_sums as 
(

Select loc_id, rend_addr_id, count(cptcode) as total_est from
(
Select loc_id, rend_addr_id, cptcode from archive_Transactions at1
where date1 between :Start_date and :end_Date and  (cptcode between '99391' and '99395') and at1.archive_Flag = 1
) group by loc_id,  rend_addr_id


)

select loc_name, staffname, s99381, s99382, s99383, s99384,s99385,total_new, s99391,s99392,s99393,s99394,s99395, total_est, coalesce(total_new,0) + coalesce(total_est,0) as grand_total from
(
Select all_locs.loc_name, staffname, raw_sums.s99381, raw_sums.s99382, raw_sums.s99383, raw_sums.s99384, raw_sums.s99385, pns.total_new, 
raw_sums.s99391, raw_sums.s99392, raw_sums.s99393, raw_sums.s99394, raw_sums.s99395,
pes.total_est from all_locs
Left outer join raw_sums on raw_sums.locnum = all_locs.id
left outer join provider_new_sums pns on pns.loc_id = raw_sums.locnum and  pns.rend_addr_id = raw_sums.rend_addr_id
left outer join provider_est_sums pes on pes.loc_id = raw_sums.locnum and  pes.rend_addr_id = raw_sums.rend_addr_id
left outer join staff1 on staff1.staffid = raw_sums.rend_addr_id
union


select loc_name, '_Total' as staffname, sum(s99381) as t_99381, sum(s99382) as t_99382, sum(s99383) as t_99383, sum(s99384) as t_99384, sum(s99385) as t_99385, 
sum(total_new) as t_new, sum(s99391) as t_99391, sum(s99392) as t_99392, sum(s99393) as t_99393, sum(s99394) as t_99394, sum(s99395) as t_99395, sum(total_est) as t_est from
(
Select all_locs.loc_name, raw_sums.*, pns.total_new, pes.total_est from all_locs
Left outer join raw_sums on raw_sums.locnum = all_locs.id
left outer join provider_new_sums pns on pns.loc_id = raw_sums.locnum and  pns.rend_addr_id = raw_sums.rend_addr_id
left outer join provider_est_sums pes on pes.loc_id = raw_sums.locnum and  pes.rend_addr_id = raw_sums.rend_addr_id
) group by loc_name, locnum

) order by loc_name, staffname

SQL Code: MySQL

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

Copy

select loc_name, staffname, s99381, s99382, s99383, s99384,s99385,total_new, s99391,s99392,s99393,s99394,s99395, total_est, coalesce(total_new,0) + coalesce(total_est,0) as grand_total from
(
Select all_locs.loc_name, staffname, raw_sums.s99381, raw_sums.s99382, raw_sums.s99383, raw_sums.s99384, raw_sums.s99385, pns.total_new, 
raw_sums.s99391, raw_sums.s99392, raw_sums.s99393, raw_sums.s99394, raw_sums.s99395,
pes.total_est from (select id, loc_name from location where archive_flag <> 1)  all_locs
Left outer join (select location.id as locnum, p.rend_addr_id, s99381, s99382, s99383, s99384, s99385, s99391, s99392, s99393, s99394, s99395 from location
left outer join
 (select distinct loc_id, rend_addr_id from archive_transactions where date1 between :start_date and :end_date and cptcode between '99381' and '99395') p on p.loc_id = location.id
left outer join
 (select loc_id, rend_addr_id,count(cptcode) as s99381 from archive_transactions where cptcode = '99381' and archive_Flag = 1 and date1 between  :start_date and :end_date group by loc_id, rend_addr_id ) a on a.loc_id = location.id and a.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99382 from archive_transactions where cptcode = '99382' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) b on b.loc_id = location.id and   b.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99383 from archive_transactions where cptcode = '99383' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) c on c.loc_id = location.id and   c.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99384 from archive_transactions where cptcode = '99384' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) d on d.loc_id = location.id and   d.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99385 from archive_transactions where cptcode = '99385' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) e on e.loc_id = location.id and   e.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99391 from archive_transactions where cptcode = '99391' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) f on f.loc_id = location.id and   f.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99392 from archive_transactions where cptcode = '99392' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) g on g.loc_id = location.id and   g.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99393 from archive_transactions where cptcode = '99393' and archive_Flag = 1 and date1 between :start_date and :end_date  group by loc_id, rend_addr_id) h on h.loc_id = location.id and   h.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99394 from archive_transactions where cptcode = '99394' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) i on i.loc_id = location.id and   i.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99395 from archive_transactions where cptcode = '99395' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) j on j.loc_id = location.id and   j.rend_addr_id = p.rend_addr_id
where location.archive_flag <> 1 ) raw_sums on raw_sums.locnum = all_locs.id
left outer join staff1 on staff1.staffid = raw_sums.rend_addr_id
left outer join ( Select loc_id, rend_addr_id, count(cptcode) as total_new from ( Select loc_id, rend_addr_id, cptcode from archive_Transactions at1 where date1 between  :start_date and :end_date and  (cptcode between '99381' and '99385') and at1.archive_Flag = 1
) t1 group by loc_id,  rend_addr_id ) pns on pns.loc_id = raw_sums.locnum and  pns.rend_addr_id = raw_sums.rend_addr_id
left outer join ( Select loc_id, rend_addr_id, count(cptcode) as total_est from ( Select loc_id, rend_addr_id, cptcode from archive_Transactions at1 where date1 between  :start_date and :end_date and  (cptcode between '99391' and '99395') and at1.archive_Flag = 1 ) t2 group by loc_id,  rend_addr_id
) pes on pes.loc_id = raw_sums.locnum and  pes.rend_addr_id = raw_sums.rend_addr_id
union

select loc_name, '_Total' as staffname, sum(s99381) as t_99381, sum(s99382) as t_99382, sum(s99383) as t_99383, sum(s99384) as t_99384, sum(s99385) as t_99385, 
sum(total_new) as t_new, sum(s99391) as t_99391, sum(s99392) as t_99392, sum(s99393) as t_99393, sum(s99394) as t_99394, sum(s99395) as t_99395, sum(total_est) as t_est from
(
Select all_locs.loc_name, raw_sums.*, pns.total_new, pes.total_est from (select id, loc_name from location where archive_flag <> 1) all_locs
Left outer join (
select location.id as locnum, p.rend_addr_id, s99381, s99382, s99383, s99384, s99385, s99391, s99392, s99393, s99394, s99395 from location
left outer join
 (select distinct loc_id, rend_addr_id from archive_transactions where date1 between :start_date and :end_date and cptcode between '99381' and '99395') p on p.loc_id = location.id
left outer join
 (select loc_id, rend_addr_id,count(cptcode) as s99381 from archive_transactions where cptcode = '99381' and archive_Flag = 1 and date1 between  :start_date and :end_date group by loc_id, rend_addr_id ) a on a.loc_id = location.id and a.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99382 from archive_transactions where cptcode = '99382' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) b on b.loc_id = location.id and   b.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99383 from archive_transactions where cptcode = '99383' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) c on c.loc_id = location.id and   c.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99384 from archive_transactions where cptcode = '99384' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) d on d.loc_id = location.id and   d.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99385 from archive_transactions where cptcode = '99385' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) e on e.loc_id = location.id and   e.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99391 from archive_transactions where cptcode = '99391' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) f on f.loc_id = location.id and   f.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99392 from archive_transactions where cptcode = '99392' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) g on g.loc_id = location.id and   g.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99393 from archive_transactions where cptcode = '99393' and archive_Flag = 1 and date1 between :start_date and :end_date  group by loc_id, rend_addr_id) h on h.loc_id = location.id and   h.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99394 from archive_transactions where cptcode = '99394' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) i on i.loc_id = location.id and   i.rend_addr_id = p.rend_addr_id
left outer join
 (select loc_id, rend_addr_id,  count(cptcode) as s99395 from archive_transactions where cptcode = '99395' and archive_Flag = 1 and date1 between  :start_date and :end_date  group by loc_id, rend_addr_id) j on j.loc_id = location.id and   j.rend_addr_id = p.rend_addr_id
where location.archive_flag <> 1
) raw_sums on raw_sums.locnum = all_locs.id
left outer join ( Select loc_id, rend_addr_id, count(cptcode) as total_new from ( Select loc_id, rend_addr_id, cptcode from archive_Transactions at1 where date1 between  :start_date and :end_date and  (cptcode between '99381' and '99385') and at1.archive_Flag = 1
) t3  group by loc_id,  rend_addr_id ) pns on pns.loc_id = raw_sums.locnum and  pns.rend_addr_id = raw_sums.rend_addr_id
left outer join ( Select loc_id, rend_addr_id, count(cptcode) as total_est from ( Select loc_id, rend_addr_id, cptcode from archive_Transactions at1 where date1 between  :start_date and :end_date and  (cptcode between '99391' and '99395') and at1.archive_Flag = 1 ) t4 group by loc_id,  rend_addr_id
) pes on pes.loc_id = raw_sums.locnum and  pes.rend_addr_id = raw_sums.rend_addr_id
) t5 group by loc_name, locnum

) t6 order by loc_name, staffname