About

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

Caveats

• Vaccines without a matching CPT on the same date of service will flag as unbilled.  The appropriate CPT is determined by the assignment in Vaccine Setup.
• Vaccines that are still in superbill status will show as unbilled.
• Vaccines you billed, but then voided, will show as unbilled.
• Vaccines that are documented as given on a separate date than the date of service on which they were billed will appear as unbilled. These are marked with the column "delta", where "delta" is the number of days between the billed vaccine and the administered vaccine. For example, if Wanda gives an MMR on 4/14, but the billed date of service is 4/16, “delta” will be 2. Note: the date the claim was created is irrelevant here. We only expect the billed DOS to be the same as the day the vaccine was administered. 
• If you use a bidirectional IIS, this report may need to be modified so that it does not show historical/legacy vaccines.  This particular report does work for many bidirectional IIS but not necessarily all of them.

SQL Code: Firebird

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

Copy

select b.* from
(
select distinct fname, lname, a.*, admin_date - billed_date as delta from (
SELECT VACCINE1_1.PATNO, VACCINE1_1.VACNAME,
       VACCINE1_1.VACDATE as admin_date, VACCINE1_1.VFC1,
       VACCINE1_1.ID, vac_lot_id, VACCINE1_1.ADMIN_ROUTE,
       VACCINE1_1.CVXCODE, VACCINE1_1.OFFCODE,
       ARCHIVE_TRANSACTIONS_1.CPTCODE,
       ARCHIVE_TRANSACTIONS_1.DATE1 as billed_date,
       VACCINE1_1.S_ADDR_ID, STAFF1_1.STAFFNAME
FROM VACCINE1 VACCINE1_1
      LEFT OUTER JOIN (select patno, date1, cptcode from ARCHIVE_TRANSACTIONS where cptcode between '90300' and '91399' and archive_flag <=1) ARCHIVE_TRANSACTIONS_1 ON
     (ARCHIVE_TRANSACTIONS_1.PATNO = VACCINE1_1.PATNO)
      AND abs(ARCHIVE_TRANSACTIONS_1.DATE1 - VACCINE1_1.VACDATE) <15
   and archive_transactions_1.cptcode=vaccine1_1.offcode
      INNER JOIN VACNAMES VACNAMES_1 ON
     (VACNAMES_1.VACNYC = VACCINE1_1.CVXCODE)
      INNER JOIN STAFF1 STAFF1_1 ON
     (STAFF1_1.STAFFID = VACCINE1_1.S_ADDR_ID)
WHERE
      ( VACCINE1_1.VACDATE BETWEEN :start_date and :end_date )
 and vac_lot_id >1
ORDER BY VACCINE1_1.PATNO, VACCINE1_1.VACDATE,
         VACCINE1_1.VACNAME
) a
inner join register on register.patno = a.patno
where lname not in ('TEST', 'TESTPATIENT')
) b where (delta is null or delta <> 0)

SQL Code: MySQL

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

Copy

select b.* from
(
select distinct fname, lname, a.*, admin_date - billed_date as delta from (
SELECT VACCINE1_1.PATNO, VACCINE1_1.VACNAME,
       VACCINE1_1.VACDATE as admin_date, VACCINE1_1.VFC1,
       VACCINE1_1.ID, vac_lot_id, VACCINE1_1.ADMIN_ROUTE,
       VACCINE1_1.CVXCODE, VACCINE1_1.OFFCODE,
       ARCHIVE_TRANSACTIONS_1.CPTCODE,
       ARCHIVE_TRANSACTIONS_1.DATE1 as billed_date,
       VACCINE1_1.S_ADDR_ID, STAFF1_1.STAFFNAME
FROM VACCINE1 VACCINE1_1
      LEFT OUTER JOIN (select patno, date1, cptcode from ARCHIVE_TRANSACTIONS where cptcode between '90300' and '91399' and archive_flag <=1) ARCHIVE_TRANSACTIONS_1 ON
     (ARCHIVE_TRANSACTIONS_1.PATNO = VACCINE1_1.PATNO)
      AND abs(timestampdiff(day, VACCINE1_1.VACDATE,ARCHIVE_TRANSACTIONS_1.DATE1 )) <15
   and archive_transactions_1.cptcode=vaccine1_1.offcode
      INNER JOIN VACNAMES VACNAMES_1 ON
     (VACNAMES_1.VACNYC = VACCINE1_1.CVXCODE)
      INNER JOIN STAFF1 STAFF1_1 ON
     (STAFF1_1.STAFFID = VACCINE1_1.S_ADDR_ID)
WHERE
      ( VACCINE1_1.VACDATE BETWEEN :start_date and :end_date )
 and vac_lot_id >1
ORDER BY VACCINE1_1.PATNO, VACCINE1_1.VACDATE,
         VACCINE1_1.VACNAME
) a
inner join register on register.patno = a.patno
where lname not in ('TEST', 'TESTPATIENT')
) b where (delta is null or delta <> 0)