CHIP Patients List

We have a state-funded Children’s Health Insurance Program (CHIP) that is offered via a number of commercial insurers.  Like all states, the public money is quickly drying up, so insurers are using third parties to scour their records to find those patients for whom
“erroneous” payments were made.  Aetna is using a company called Aftermath to do this with CHIP patients, but it has been a disaster as the company is making what appears to be serious false assumptions.

We need to identify those CHIP clients under any insurer so that we can check benefits before every visit.

The only way to identify these clients is to have a query that identifies patients younger than 19 who have insurance policies in which they are also the subscriber. The list should include only patients who have received services in the last 120 days.

SELECT DISTINCT
  a.lastname,a.firstname,a.id
FROM
  sos.patients a
  JOIN sos.rv_policies b ON a.ptnum = b.ptnum
  JOIN sos.journal c ON a.ptnum = c.ptnum
WHERE
  sos.AgeInYears(a.dob,today()) < 19
  AND reltoinsd = '01'
  AND c.trantype = 'S'
  AND c.trandate BETWEEN (TODAY() - 120) AND TODAY()
ORDER BY a.lastname,a.firstname,a.id

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.