About

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

Caveats

• The “last WCC” date is the most recent date a well visit note was entered in OP.  It is blank if the child has never had a checkup documented as an OP visit note.
• The “visits since last checkup” is computed as the number of distinct dates on which any billed service has been provided to the patient after the “last WCC” date.

• If the child’s “last WCC” date is blank, the number will include all billed dates of service.
• Note that not all “billed services” may constitute an actual visit. Example: your practice bills for non-service charges like no-show fees, medical records, and so on.
• A value of 0 means that the child has had no billed services on any date since his/her last WCC date.

SQL Code: Firebird

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

Copy

with wcc as
(
select patno, max(date1) as last_pe from physical inner join register on register.patno= physical.patno where status_pat = 'ACTIVE' group by patno),
previous_visits_for_seen as 
(

select patno, count(n) as visits_since from
(
select distinct at1.patno, last_pe, at1.date1 as n from archive_transactions at1
inner join register on register.patno = at1.patno
left outer join wcc on wcc.patno = at1.patno
where at1.cptcode not in ('1','2','3','4') and at1.archive_flag = 1 and at1.date1 > last_pe
and register.status_pat = 'ACTIVE'
order by patno) group by patno
), prev_all as
(
select patno, count(n) as visits_all from
(
select distinct at1.patno, last_pe, at1.date1 as n from archive_transactions at1
inner join register on register.patno = at1.patno
left outer join wcc on wcc.patno = at1.patno
where at1.cptcode not in ('1','2','3','4') and at1.archive_flag = 1 
and register.status_pat = 'ACTIVE'
order by patno) group by patno
)

select patno, last_pe as last_wcc,  case when last_pe is null then visits_all else v end as visits_since_last_checkup from
(
select register.patno, last_pe, coalesce(visits_since,0) as v, visits_all from register
left outer join wcc on wcc.patno = register.patno
left outer join previous_visits_for_seen on previous_visits_for_seen.patno = register.patno
left outer join prev_all on prev_all.patno = register.patno
 where register.status_pat = 'ACTIVE' and lname not in ('TESTPATIENT') and patno >99
)

SQL Code: MySQL

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

Copy

select patno,cast( last_pe as date) as last_wcc,  case when last_pe is null then visits_all else v end as visits_since_last_checkup from
(
select register.patno, last_pe, coalesce(visits_since,0) as v, visits_all from register
left outer join (
select physical.patno, max(date1) as last_pe from physical inner join register on register.patno= physical.patno where status_pat = 'ACTIVE' group by patno) wcc on wcc.patno = register.patno
left outer join (

select patno, count(n) as visits_since from
(
select distinct at1.patno, last_pe, at1.date1 as n from archive_transactions at1
inner join register on register.patno = at1.patno
left outer join (
select physical.patno, max(date1) as last_pe from physical inner join register on register.patno= physical.patno where status_pat = 'ACTIVE' group by patno) wcc on wcc.patno = at1.patno
where at1.cptcode not in ('1','2','3','4') and at1.archive_flag = 1 and at1.date1 > last_pe
and register.status_pat = 'ACTIVE'
order by patno) t3 group by patno
) previous_visits_for_seen on previous_visits_for_seen.patno = register.patno
left outer join (
select patno, count(n) as visits_all from
(
select distinct at1.patno, last_pe, at1.date1 as n from archive_transactions at1
inner join register on register.patno = at1.patno
left outer join (
select physical.patno, max(date1) as last_pe from physical inner join register on register.patno= physical.patno where status_pat = 'ACTIVE' group by patno) wcc on wcc.patno = at1.patno
where at1.cptcode not in ('1','2','3','4') and at1.archive_flag = 1
and register.status_pat = 'ACTIVE'
order by patno) t2 group by patno
) prev_all on prev_all.patno = register.patno
 where register.status_pat = 'ACTIVE' and lname not in ('TESTPATIENT')  and register.patno >99
) t1