| 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 possible chart errors for active patients in terms of their assigned VFC status (1= Medicaid, 2=self pay). It does not address VFC assignment errors for AIAN children, underinsured children, or CHIP children.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- While most states’ VFC programs use these definitions, OP practices in universal purchase states or who use non-standard definitions of, for example, “uninsured”, may find this report misleading.
- Replace the comma-delimited list ('TCC','BLU','TNS','AGR') with your practice’s list of Medicaid plan codes. Each code should be enclosed in single quotes and separated by commas. If you only have one Medicaid plan PLN, your list will look like: (‘PLN’). The Medicaid list will need to be replaced in all six instances in which it appears in the code.
- Any patient with VFC 1,2,3,4 who is 19+ years old will show as “Too old for VFC.”
- Any patient under 19 who is self-pay will show as “Self pay child - should be VFC 2.”
- Any patient with primary, secondary, or tertiary insurance in your Medicaid payer list who is under 19 and does not have VFC=1 will show as “Medicaid - should be VFC eligible.”
- Any patient who has VFC=1 but does not show a primary, secondary, or tertiary insurance in your Medicaid payer list will show as “No Medicaid - not VFC eligible.”
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
with c as
(select patno, fname,lname, birthdat, sickvno as vfc, ins_carrier_code, ins2_carrier_code, ins_carrier_code_3,
floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,cast('today' as date)) )/12) as age
from register
where status_pat = 'ACTIVE' and patno >99 and lname <> 'TESTPATIENT')
select c.*, 'Too old for VFC' as problem from c where age >=19 and vfc in (1,2,3,4)
union
select c.*, 'Medicaid - should be VFC elig' as problem from c where age <19 and vfc not in (1) and
( ins_carrier_code in ('TCC','BLU','TNS','AGR') or ins2_carrier_code in ('TCC','BLU','TNS','AGR') or ins_carrier_code_3 in ('TCC','BLU','TNS','AGR'))
union
select c.*, 'Self pay child - should be VFC 2' as problem from c where age <19 and vfc <> 2 and
( ins_carrier_code in ('SP') or ins2_carrier_code in ('SP') or ins_carrier_code_3 in ('SP'))
union
select c.*, 'No Medicaid - not VFC elig' as problem from c where age <19 and vfc in (1) and
( ins_carrier_code not in ('TCC','BLU','TNS','AGR') and ins2_carrier_code not in ('TCC','BLU','TNS','AGR') and ins_carrier_code_3 not in ('TCC','BLU','TNS','AGR'))SQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select c.*, 'Too old for VFC' as problem from (select patno, fname,lname, birthdat, sickvno as vfc, ins_carrier_code, ins2_carrier_code, ins_carrier_code_3, timestampdiff(year,birthdat,curdate()) as age from register where status_pat = 'ACTIVE' and patno >99 and lname <> 'TESTPATIENT')
c where age >=19 and vfc in (1,2,3,4)
union
select c.*, 'Medicaid - should be VFC elig' as problem from (select patno, fname,lname, birthdat, sickvno as vfc, ins_carrier_code, ins2_carrier_code, ins_carrier_code_3, timestampdiff(year,birthdat,curdate()) as age from register where status_pat = 'ACTIVE' and patno >99 and lname <> 'TESTPATIENT')
c where age <19 and vfc not in (1) and
( ins_carrier_code in ('TCC','BLU','TNS','AGR') or ins2_carrier_code in ('TCC','BLU','TNS','AGR') or ins_carrier_code_3 in ('TCC','BLU','TNS','AGR'))
union
select c.*, 'Self pay child - should be VFC 2' as problem from (select patno, fname,lname, birthdat, sickvno as vfc, ins_carrier_code, ins2_carrier_code, ins_carrier_code_3, timestampdiff(year,birthdat,curdate()) as age from register where status_pat = 'ACTIVE' and patno >99 and lname <> 'TESTPATIENT')
c where age <19 and vfc <> 2 and
( ins_carrier_code in ('SP') or ins2_carrier_code in ('SP') or ins_carrier_code_3 in ('SP'))
union
select c.*, 'No Medicaid - not VFC elig' as problem from (select patno, fname,lname, birthdat, sickvno as vfc, ins_carrier_code, ins2_carrier_code, ins_carrier_code_3, timestampdiff(year,birthdat,curdate()) as age from register where status_pat = 'ACTIVE' and patno >99 and lname <> 'TESTPATIENT')
c where age <19 and vfc in (1) and
( ins_carrier_code not in ('TCC','BLU','TNS','AGR') and ins2_carrier_code not in ('TCC','BLU','TNS','AGR') and ins_carrier_code_3 not in ('TCC','BLU','TNS','AGR'))
