We are currently updating the OP Help Center content for the release of OP 14.19 or OP 19. OP 19 is a member of the certified OP 14 family of products (official version is 14.19.1), which you may see in your software (such as in Help > About) and in the Help Center tabs labeled 14.19. You may also notice that the version number in content and videos may not match the version of your software, and some procedural content may not match the workflow in your software. We appreciate your patience and understanding as we make these enhancements.

SQL: Find Patients with Insurance Prefix

About

This report looks for all active patients who have an active insurance policy that contains a specified prefix. The SQL code consists of the prefix ‘ABC’, but it can be altered to find the insurance policy prefix you desire.

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

Caveats

In the third line, 'ABC%' must be changed to properly run this code. You can do one of the following:

  • Change the letters ‘ABC%’ to look for your desired insurance policy prefix.  For example, if you are looking for all patients whose insurance policy number begins with WT3, change the third line to:

where INSURED_ID like 'WT3%' 

  • If you are looking for a sequence of letters or characters that appear anywhere in the insurance policy (not just at the beginning [prefix]), add a % sign to the beginning of the alphanumeric sequence.  For example, if you are looking for patients whose insurance policy includes the sequence 156 anywhere in the policy number, change the third line to:

where INSURED_ID like '%156%' 

Code

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

select PATNO,fname,lname,INS_CARRIER_NAME,insured1.INSURED_ID from insured1
inner join register on register.patno = insured1.patno
where insured1.INSURED_ID like 'ABC%'
and status_policy ='ACTIVE'
and status_pat = 'ACTIVE'