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
